T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!

playyuer 2004-08-20 04:02:14
这两个小技巧,不写不知道,一写吓一跳!
都是看似简单,实际做起来就懵,得仔细想一想,才能写对!
凡是有日期运算的程序都要细心哦!

希望有更好的思路!

《T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!》
http://www.csdn.net/Develop/read_article.asp?id=33740

最后再作一下广告:
http://www.microshaoft.com
http://www.csdn.net/Develop/list_article.asp?author=playyuer

送你几个巨长的 SQL,也许狠有用,点一下,帮我加点儿人气:

T-SQL 生成 两个新的真正的公历年历
http://www.csdn.net/Develop/Read_Article.asp?Id=26447


T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
http://www.csdn.net/Develop/Read_Article.asp?Id=26083


http://www.microshaoft.com
http://www.csdn.net/Develop/list_article.asp?author=playyuer

...全文
2350 50 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
50 条回复
切换为时间正序
请发表友善的回复…
发表回复
老宛 2004-08-27
  • 打赏
  • 举报
回复
收藏
egxsun 2004-08-26
  • 打赏
  • 举报
回复
学习!
zhjboss 2004-08-26
  • 打赏
  • 举报
回复
学习、
zhangzs8896 2004-08-24
  • 打赏
  • 举报
回复
这个要收藏
LJWS 2004-08-24
  • 打赏
  • 举报
回复
MARK
健者天行 2004-08-24
  • 打赏
  • 举报
回复
先 mark
outwindows 2004-08-24
  • 打赏
  • 举报
回复
prcgolf 2004-08-24
  • 打赏
  • 举报
回复
up
playyuer 2004-08-24
  • 打赏
  • 举报
回复
set datefirst 7 的

请问有没有统计一个时间段星期几的个数的函数?急、、、、、
http://search.csdn.net/expert/topicview1.asp?id=2585071

pisces007 2004-08-22
  • 打赏
  • 举报
回复
好些天没来,看来错过了很多好戏啊
......
playyuer 2004-08-21
  • 打赏
  • 举报
回复
看来确实不是我小题大做!
playyuer 2004-08-21
  • 打赏
  • 举报
回复
代 zjcxc(邹建) 整理一下:

declare @dt1 datetime,@dt2 datetime
select @dt1='2004-07-29'
,@dt2='2004-08-05'

--查询方式2,横向
select 跨周数
,周一=case d
when 0 then case when b=1 then 1 else 0 end
when 1 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case d
when 0 then case when b=2 then 1 else 0 end
when 1 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case d
when 0 then case when b=3 then 1 else 0 end
when 1 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case d
when 0 then case when b=4 then 1 else 0 end
when 1 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case d
when 0 then case when b=5 then 1 else 0 end
when 1 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case d
when 0 then case when b=6 then 1 else 0 end
when 1 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case d
when 0 then case when b=0 then 1 else 0 end
when 1 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=(datediff(day,@dt1,@dt2)+7)/7
,a=(datediff(day,@dt1,@dt2))/7-1
,b=(@@datefirst+datepart(weekday,@dt1)-1)%7
,c=(@@datefirst+datepart(weekday,@dt2)-1)%7
,d=datediff(week,@dt1,@dt2)
)a


--查询方式1,纵向
select 项目='跨周数',值=(datediff(day,@dt1,@dt2)+7)/7
union all
select a.a,case d when 0
then
case when a.b between b.b and b.c
then 1
else 0
end
when 1
then
case when b.b<=a.b
then 1
else 0
end
+
case when b.c>=a.b
then 1
else 0
end
else b.a
+ case when b.b<=a.b then 1 else 0 end
+ case when b.c>=a.b then 1 else 0 end
end
from
(
select a='星期一',b=1
union all
select '星期二',2
union all
select '星期三',3
union all
select '星期四',4
union all
select '星期五',5
union all
select '星期六',6
union all select '星期日',0
) a
,
(select a=(datediff(day,@dt1,@dt2)+7)/7-1
,b=(@@datefirst+datepart(weekday,@dt1)-1)%7
,c=(@@datefirst+datepart(weekday,@dt2)-1)%7
,d=datediff(week,@dt1,@dt2)) b

zjcxc 元老 2004-08-21
  • 打赏
  • 举报
回复
我的想法就是截头去尾
因为datediff不受 set datefirst 的影响,固定一周从周日到周六
所以我的想法也就是通过 @@datefirst 与 datepart(weekday,@dt) 的值,换算成固定的每周从0(星期天)到6(星期六)的数字,按下面的规则进行统计

datediff(week,@dt_begin,@dt_end) 的结果
如果是为0,表示没有跨周
那么统计的时候,就从 @dt_begin 到 @dt_end 之间的星期x有数,其他都没有数

如果是为1,表示跨周,但两个周都不完整
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数

如果是>1,表示跨周,而且中间有完整的周
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数
+完整的周数
zjcxc 元老 2004-08-21
  • 打赏
  • 举报
回复
--重新来过,上面的还是有问题

--查询参数
declare @dt_begin datetime,@dt_end datetime
select @dt_begin='2004-8-2',@dt_end='2004-8-15'

--查询方式1(纵向)
select 项目='跨周数'
,值=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end
)b

--查询方式2(横向)
select 跨周数
,周一=case a
when -1 then case when b=1 then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when b=2 then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when b=3 then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when b=4 then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when b=5 then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when b=6 then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when b=0 then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
,a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
playyuer 2004-08-21
  • 打赏
  • 举报
回复
截止到2004-08-21 :

一共三板斧:
1,2 是 邹建的

邹建的想法就是截头去尾
因为datediff不受 set datefirst 的影响,固定一周从周日到周六
所以我的想法也就是通过 @@datefirst 与 datepart(weekday,@dt) 的值,换算成固定的每周从0(星期天)到6(星期六)的数字,按下面的规则进行统计

datediff(week,@dt_begin,@dt_end) 的结果
如果是为0,表示没有跨周
那么统计的时候,就从 @dt_begin 到 @dt_end 之间的星期x有数,其他都没有数

如果是为1,表示跨周,但两个周都不完整
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数

如果是>1,表示跨周,而且中间有完整的周
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数
+完整的周数


declare @b datetime,@e datetime
select @b='2002-8-5',@e='2008-7-15'

--查询方式1(纵向)
select
项目='跨周数'
,值=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end
)b


--查询方式2(横向)
select @b as 开始日期,@e as 结束日期
,跨周数
,周一=case a
when -1 then case when b=1 then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when b=2 then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when b=3 then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when b=4 then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when b=5 then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when b=6 then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when b=0 then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
,a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end)a

--3 是 playyuer 的

select @b as 开始日期,@e as 结束日期,
datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1 as 跨周数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期二','星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
as 周一个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一') then 1
else 0 end
as 周二个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二') then 1
else 0 end
as 周三个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三') then 1
else 0 end
as 周四个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四') then 1
else 0 end
as 周五个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@b) in ('星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五') then 1
else 0 end
as 周六个数

,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1

- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五','星期六') then 1
else 0 end

as 周日个数

txhack 2004-08-21
  • 打赏
  • 举报
回复
向高手学习!
zjcxc 元老 2004-08-20
  • 打赏
  • 举报
回复
算多了一天(跨度超过2周的

a=(datediff(day,@dt1,@dt2)+7)/7-1

改为:

select a=(datediff(day,@dt1,@dt2))/7-1

zjcxc 元老 2004-08-20
  • 打赏
  • 举报
回复
--查询的参数
declare @dt1 datetime,@dt2 datetime
select @dt1='2004-08-01 00:00:00.000'
,@dt2='2004-08-11 00:00:00.000'

--查询方式2,横向
select 跨周数
,周一=case d
when 0 then case when b=1 then 1 else 0 end
when 1 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case d
when 0 then case when b=2 then 1 else 0 end
when 1 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case d
when 0 then case when b=3 then 1 else 0 end
when 1 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case d
when 0 then case when b=4 then 1 else 0 end
when 1 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case d
when 0 then case when b=5 then 1 else 0 end
when 1 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case d
when 0 then case when b=6 then 1 else 0 end
when 1 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case d
when 0 then case when b=0 then 1 else 0 end
when 1 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=(datediff(day,@dt1,@dt2)+7)/7
,a=(datediff(day,@dt1,@dt2)+7)/7-1
,b=(@@datefirst+datepart(weekday,@dt1)-1)%7
,c=(@@datefirst+datepart(weekday,@dt2)-1)%7
,d=datediff(week,@dt1,@dt2)
)a
zjcxc 元老 2004-08-20
  • 打赏
  • 举报
回复
--干部的方法,如果用sp_language设置了区域判断起来还是会出问题

--所以我觉得还是回到判断 @@datefirst 上来处理,这样就不受SQL配置的影响

--下面是两种处理方法

--查询的参数
declare @dt1 datetime,@dt2 datetime
select @dt1='2004-08-01 00:00:00.000'
,@dt2='2004-08-11 00:00:00.000'

--查询方式1,纵向
select 项目='跨周数',值=(datediff(day,@dt1,@dt2)+7)/7
union all
select a.a,case d
when 0
then case when a.b between b.b and b.c then 1 else 0 end
when 1
then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a
+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=(datediff(day,@dt1,@dt2)+7)/7-1
,b=(@@datefirst+datepart(weekday,@dt1)-1)%7
,c=(@@datefirst+datepart(weekday,@dt2)-1)%7
,d=datediff(week,@dt1,@dt2))b
futulove 2004-08-20
  • 打赏
  • 举报
回复
加载更多回复(30)

34,838

社区成员

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

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