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

http://blog.csdn.net/playyuer/archive/2004/08/20/79699.aspx

2.根据出生日期精确计算年龄!
所谓计算精确年龄就是: 生日差一天也不能长一岁!
大家常用,间隔年数算作年龄! 如果需求要精确,如: 保险 之类的,就粗了!
当然还可引申为根据入职日期计算精确的司龄,算加薪之类的需求!
我起初认为很简单,当年也写了好几遍才写对!高手们也被我晃点了数次几近晕倒!
不信有当年 2002-11-27 16:16:26 贴子为证:

《我的表中有字段出生年月日,请问怎样根据当前日期计算该人的年龄,并填充到表的相应字段内?》

http://search.csdn.net/expert/topicview1.asp?id=1210302

书归正传:

declare @Birthday smalldatetime
set @Birthday ='1949-6-5'

--多用一个日期变量,为了扩展: 计算将来或过去某一时刻的当时的精确年龄 的功能!
declare @ smalldatetime
set @ = getdate()

select datediff(year,@Birthday,@)
+ case when dateadd(year,datediff(year,@Birthday,@),@Birthday) <= @
then 1
else 0
end

1.计算任意两日期之间的 周一 到 周日 分别各有几个!
起因来自该贴:
《SQL语句求当月的上班天数》

http://community.csdn.net/Expert/TopicView1.asp?id=3291510

《求一尖酸的算法》(有点儿同感,说起来简单,做起来就晕)

http://www.itpub.net/252645.html

顺便说一下,我写程序总想写的"更通用"一些,可能答非所问了!

declare @b datetime
declare @e datetime

set @b = '2004-07-29'
set @e = '2004-08-05'

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

--哇塞终于搞定了,起初没想到的是: 竟然着实动了一番脑筋!

T-SQL 生成 两个新的真正的公历年历