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

[playyuer]

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

希望有更好的思路!

《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

[futulove]

[zjcxc]

--干部的方法,如果用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

[zjcxc]

--查询的参数
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]

算多了一天(跨度超过2周的

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

改为:

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

[txhack]

向高手学习!

[playyuer]

截止到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 周日个数

[zjcxc]

--重新来过,上面的还是有问题

--查询参数
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

[zjcxc]

我的想法就是截头去尾
因为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的数
+完整的周数

[playyuer]

代 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

[playyuer]

看来确实不是我小题大做!

[pisces007]

好些天没来，看来错过了很多好戏啊
......

[playyuer]

set datefirst 7 的

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

[prcgolf]

up

[outwindows]

[健者天行]

先 mark

[LJWS]

MARK

[zhangzs8896]

这个要收藏

[zhjboss]

学习、

[egxsun]

学习！

[老宛]

收藏