set @dt='2009-03-01 00:00:00' SELECT case when datepart(w,CONVERT(datetime,CONVERT(char(8),@dt,120)+'1'))=1 then dateadd(day,+7,@dt) else datepart(w,CONVERT(datetime,CONVERT(char(8),@dt,120)+'1'))+7 end
select a from ( select '2009-3-1' a union all select '2009-3-2'union all select '2009-3-3'union all select '2009-3-4'union all select '2009-3-5'union all select '2009-3-6'union all select '2009-3-7'union all select '2009-3-8'union all select '2009-3-9'union all select '2009-3-10'union all select '2009-3-11'union all select '2009-3-12'union all select '2009-3-13'union all select '2009-3-14'union all select '2009-3-15'union all select '2009-3-16'union all select '2009-3-17'union all select '2009-3-18'union all select '2009-3-19'union all select '2009-3-20'union all select '2009-3-21'union all select '2009-3-22'union all select '2009-3-23'union all select '2009-3-24'union all select '2009-3-25'union all select '2009-3-26'union all select '2009-3-27'union all select '2009-3-28'union all select '2009-3-29'union all select '2009-3-30'union all select '2009-3-31') as a where DATEDIFF(wk,'2009-3-1',a) =2 -- 第2周 and DATEPART(weekday, a)=1 -- 周日
--第一题 declare @s datetime set @s='2009-03-01' select case when DATEPART(WEEKDAY,@s)=1 then DATEadd(day,7,@s) else dateadd(day,14-DATEPART(WEEKDAY,@s)+1,@s) end --第二题 declare @s2 datetime set @s2='2009-11-01' select case when DATEPART(WEEKDAY,@s2)=1 then @s2 else dateadd(day,7-DATEPART(WEEKDAY,@s2)+1,@s2) end /* 2009-03-08 00:00:00.000(1 行受影响) ----------------------- 2009-11-01 00:00:00.000 */
declare @t varchar(10)
set @t = '200605'
select 18 - datepart(weekday, @t + '01')%7
-----------
16
(所影响的行数为 1 行)
select dateadd(d,18-datepart(weekday, @t + '01')%7 ,@t+'01')
--这个返回的是该月的第三个星期三
------------------------------------------------------
2006-05-17 00:00:00.000
(所影响的行数为 1 行)
declare @d datetime
set @d='20090301'
select @d+14-datepart(weekday,@d+@@datefirst-1)set @d='20091101'
select @d+7-datepart(weekday,@d+@@datefirst-1)-----------------------
2009-03-08 00:00:00.000
-----------------------
2009-11-01 00:00:00.000
set @dt='2009-03-01 00:00:00'
SELECT case when datepart(w,CONVERT(datetime,CONVERT(char(8),@dt,120)+'1'))=1 then dateadd(day,+7,@dt)
else datepart(w,CONVERT(datetime,CONVERT(char(8),@dt,120)+'1'))+7 end
select a from (
select '2009-3-1' a union all
select '2009-3-2'union all
select '2009-3-3'union all
select '2009-3-4'union all
select '2009-3-5'union all
select '2009-3-6'union all
select '2009-3-7'union all
select '2009-3-8'union all
select '2009-3-9'union all
select '2009-3-10'union all
select '2009-3-11'union all
select '2009-3-12'union all
select '2009-3-13'union all
select '2009-3-14'union all
select '2009-3-15'union all
select '2009-3-16'union all
select '2009-3-17'union all
select '2009-3-18'union all
select '2009-3-19'union all
select '2009-3-20'union all
select '2009-3-21'union all
select '2009-3-22'union all
select '2009-3-23'union all
select '2009-3-24'union all
select '2009-3-25'union all
select '2009-3-26'union all
select '2009-3-27'union all
select '2009-3-28'union all
select '2009-3-29'union all
select '2009-3-30'union all
select '2009-3-31') as a
where DATEDIFF(wk,'2009-3-1',a) =2 -- 第2周
and DATEPART(weekday, a)=1 -- 周日
declare @s datetime
set @s='2009-03-01'
select case when DATEPART(WEEKDAY,@s)=1 then DATEadd(day,7,@s)
else dateadd(day,14-DATEPART(WEEKDAY,@s)+1,@s) end
--第二题
declare @s2 datetime
set @s2='2009-11-01'
select case when DATEPART(WEEKDAY,@s2)=1 then @s2
else dateadd(day,7-DATEPART(WEEKDAY,@s2)+1,@s2) end
/*
2009-03-08 00:00:00.000(1 行受影响)
-----------------------
2009-11-01 00:00:00.000
*/