每月星期1

[twinpat]

如何将每月的

[dawugui]

/*
功能: 计算在某一段时间内某周几（如星期一）的所有日期
设计：OK_008
时间：2008-01
*/
DECLARE @Date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WeekDay int
DECLARE @i int

SET DATEFIRST 7 --设置每周的第一天 
SET @StartDate='2008-01-01' --统计的开始日期
SET @EndDate='2008-12-31' --统计的结束日期
SET @WeekDay=2 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二
SET @i=DATEPART(weekday,@StartDate)
PRINT '每周的第1天设置@@DATEFIRST: '+CAST(@@DATEFIRST AS nvarchar(1))
PRINT '开始日期对应一周的第几天: '+CAST(@i AS nvarchar(1))
IF(@i<=@WeekDay AND @i<7)
SET @i=@WeekDay-@i 
ELSE IF(@i<=@WeekDay AND @i=7)
SET @i=@i-@WeekDay
ELSE
SET @i=@@DATEFIRST-@i+@WeekDay

SET @Date=DATEADD(day,@i,@StartDate)
WHILE @Date<=@EndDate 
BEGIN
IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121)
SET @Date=DATEADD(Week,1,@Date)
END
GO

/*
每周的第1天设置@@DATEFIRST: 7
开始日期对应一周的第几天: 3
2008-01-07
2008-01-14
2008-01-21
2008-01-28
2008-02-04
2008-02-11
2008-02-18
2008-02-25
2008-03-03
2008-03-10
2008-03-17
2008-03-24
2008-03-31
2008-04-07
2008-04-14
2008-04-21
2008-04-28
2008-05-05
2008-05-12
2008-05-19
2008-05-26
2008-06-02
2008-06-09
2008-06-16
2008-06-23
2008-06-30
2008-07-07
2008-07-14
2008-07-21
2008-07-28
2008-08-04
2008-08-11
2008-08-18
2008-08-25
2008-09-01
2008-09-08
2008-09-15
2008-09-22
2008-09-29
2008-10-06
2008-10-13
2008-10-20
2008-10-27
2008-11-03
2008-11-10
2008-11-17
2008-11-24
2008-12-01
2008-12-08
2008-12-15
2008-12-22
2008-12-29
*/

[twinpat]

顶。。

[wzy_love_sly]

declare @dt datetime
set @dt='2008-01-01'
select case 
when (datepart(dw,@dt)+@@datefirst-1)%7=1 then @dt else @dt+(8-(datepart(dw,@dt)+@@datefirst-1)%7) end

2008-01-07 00:00:00.000

[wzy_love_sly]

declare @dt datetime
set @dt='2008-02-01'
select case 
when (datepart(dw,@dt)+@@datefirst-1)%7=1 then @dt else @dt+(8-(datepart(dw,@dt)+@@datefirst-1)%7) end

2008-02-04 00:00:00.000

[Limpire]

declare @Date datetime, @FirstMonday datetime
set @Date = '2008-04-01'
set @FirstMonday = @Date+(8-(@@datefirst+datepart(weekday,@Date)-1)%7)%7
select @FirstMonday, datename(weekday,@FirstMonday)

[ai19811125]

create proc zhouri(@y varchar(10))
as
declare @d varchar(20),@m varchar(2)
set @d=@y+'-01-01'
set @m='1'
if not object_id('tempdb..#ta') is null
drop table #ta
create table #ta(dtime varchar(30))
while(Convert(int,@m)<13)
begin
if(datepart(weekday,@d)=1)//此处判断查询的是星期几
begin
insert into #ta select @d
set @m=@m+1
set @d=@y+'-'+@m+'-01'
end
else
set @d=Convert(varchar,Convert(datetime,@d)+1)
end
select Convert(datetime,dtime) from #ta

[huangqing_80]

这个时候大乌龟的贴子总是飞流直下三千尺啊

[conan304]

create procedure proc_everyMonday
(@month char(6))
AS
create table #(col datetime)

declare @start datetime,@end datetime
select @start=@month+'01',@end=dateadd(day,-1,dateadd(month,1,@start))

while(@start<=@end)
begin
	if(datename(weekday,@start)='星期一')
	begin
		insert # select @start
		set @start=dateadd(day,7,@start)		
	end		
	else 
	set @start=dateadd(day,1,@start)
end

select * from #

exec proc_everyMonday '200804'
/*
col                                                    
------------------------------------------------------ 
2008-04-07 00:00:00.000
2008-04-14 00:00:00.000
2008-04-21 00:00:00.000
2008-04-28 00:00:00.000
*/