今天在看资料,讲的是根据一个人的出生日期和今天的日期来计算他最近的生日。这个问题在一定程度上演示了如何通过使用datetime函数正确处理闰年。
下面我会建一张测试表,来说明问题
1 create table testTB2 (3 ID int primary key identity (1,1),4 name varchar(20),5 birthday datetime6 )
然后插入几条数据
INSERT INTO TESTTB VALUES('yj','1990-08-20')INSERT INTO TESTTB VALUES('cyf','1990-05-20')INSERT INTO TESTTB VALUES('wzm','1990-12-20')INSERT INTO TESTTB VALUES('gzs','1990-12-31')INSERT INTO TESTTB VALUES('XXx','1972-2-29')INSERT INTO TESTTB VALUES('ww',cast(convert(char(8),getdate(),112) as datetime))
好,上面就是我现在表里面的数据。
我想要的是每个人离今天最近的生日,如果今年的生日已经过了,则返回明年的生日,否则,返回今年的生日
ok
1 with Args1 as ( 2 SELECT name,birthday,datediff(year,birthday,getdate()) as diff, 3 cast(convert(char(8),getdate(),112) as datetime)as today from testTB 4 ), 5 Args2 as 6 ( 7 select name,birthday,today,dateadd(year,diff,birthday) as bdcur, 8 dateadd(year,diff+1,birthday) as bdnxt from Args1 9 ),10 Args3 as11 (12 select name,birthday,today,bdcur+ case when day(birthday)=29 and day(bdcur)=28 then 1 else 0 end as bdcur,13 bdnxt+case when day(birthday)=29 and day(bdnxt)=28 then 1 else 0 end as bdnxt from Args214 )15 select name,birthday,case when bdcur>=today then bdcur else bdnxt end as birth from Args3
OK
最后结果是这样的
Args1用于计算每个人的出生日期与今天相差的年数(diff),今天午夜的日期(today),要计算某人最近的生日,需要把birthday加上diff列的年数,如果结果遭遇今天,需要再加一年。
Args2比Args1增加了bdcur和bdnxt两列,这2列分别用于保存今年和明年的生日。
ps:如果出生日期是2月29,而目标日期(bdcur或bdnxt)不是在闰年,这2列所包含的将是2月28,而不是3月1,Args3根据需要把bdcur和bdnxt的日期调整为3月1,。
如果bdcur大于或者等于今天的日期,外部查询返回bdnxt作为最近的生日,否则就返回bdnxt。
你可以看到最后2条特殊数据有所改变