散粉: 趁热儿送上刚写的一句日期相关的 SQL !

playyuer 2004-11-18 02:52:49

原贴:
http://community.csdn.net/Expert/topic/3564/3564281.xml?temp=.2465479

declare @ datetime
set @ = '2004-11-16 11:00:50'
declare @i int
set @i = 6

select
dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))) as [Date]
from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11
) M
,
(
select 0 as i
union all select 1 union all select 2 union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9 union all select 10
union all select 11 union all select 12 union all select 13 union all select 14
union all select 15 union all select 16 union all select 17 union all select 18
union all select 19 union all select 20 union all select 21 union all select 22
union all select 23 union all select 24 union all select 25 union all select 26
union all select 27 union all select 28 union all select 29 union all select 30
) d
where datediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))) = m.i
and (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 not in (0,1)
and
(
datediff(day,@,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) --总间隔天数
-datediff(week,@,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) + case when (@@datefirst + datepart(weekday,@)) % 7 + case when (@@datefirst + datepart(weekday,@)) % 7 = 0 then 7 else 0 end > 7 then 0 else 1 end - case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 + case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 0 then 7 else 0 end >= 7 then 0 else 1 end --as 减掉周六数
-datediff(week,@,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) + case when (@@datefirst + datepart(weekday,@)) % 7 + case when (@@datefirst + datepart(weekday,@)) % 7 = 0 then 7 else 0 end > 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 + case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 0 then 7 else 0 end >= 1 then 0 else 1 end --as 减掉周日数
) % @i = 0
order by [Date]

思路参阅:
T-SQL 生成一个简易的公历年历 T-SQL 含日期所在月及年的周次
http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx

T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!
http://blog.csdn.net/playyuer/archive/2004/08/20/79699.aspx

...全文