散粉: 趁热儿送上刚写的一句日期相关的 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
...全文
1221 180 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
180 条回复
切换为时间正序
请发表友善的回复…
发表回复
lousidao 2005-01-10
  • 打赏
  • 举报
回复
up
老宛 2005-01-10
  • 打赏
  • 举报
回复
谢谢楼主,收藏!
zyb_8022 2005-01-10
  • 打赏
  • 举报
回复
收藏
zhangzs8896 2005-01-10
  • 打赏
  • 举报
回复
才看见这帖,呵呵
passionke 2005-01-10
  • 打赏
  • 举报
回复
up
didoleo 2005-01-10
  • 打赏
  • 举报
回复
学习
zoechu520 2005-01-10
  • 打赏
  • 举报
回复
Softlee81307 2005-01-10
  • 打赏
  • 举报
回复
学习,接分
yangljing 2005-01-10
  • 打赏
  • 举报
回复
up

lousidao 2005-01-10
  • 打赏
  • 举报
回复
up
ckw675901 2005-01-04
  • 打赏
  • 举报
回复
呵呵,我来接分。
july 2005-01-04
  • 打赏
  • 举报
回复
干啥的,我还是没有明白阿
ReViSion 2005-01-04
  • 打赏
  • 举报
回复
接点,up
scapple 2005-01-01
  • 打赏
  • 举报
回复
接分!
dsclub 2005-01-01
  • 打赏
  • 举报
回复
好厉害
OracleRoob 2005-01-01
  • 打赏
  • 举报
回复
顶啊!
comefeel 2005-01-01
  • 打赏
  • 举报
回复
我要收藏,好好研究!
U2U2U2U2 2005-01-01
  • 打赏
  • 举报
回复
比较盲目
呵呵~
有些冗余
xiangshu_129 2005-01-01
  • 打赏
  • 举报
回复
好啊!
ybiori 2005-01-01
  • 打赏
  • 举报
回复
太强了!从来没想过要写这么长的一句sql,收藏研究
加载更多回复(160)

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧