使用Northwind的Employees表,根据存储的Birthdate返回每个员工离今天最近的生日,如果今年的生日已经过了,则返回明年的,反之,则返回今年的(注意,如果是闰年2月29日出生的,则在下个月的3月1号过生日)
1: WITH arg1
2: AS ( SELECT FirstName ,
3: LastName ,
4: BirthDate ,
5: DATEDIFF(YEAR, BirthDate, GETDATE()) AS DIFF ,
6: CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
7: FROM Employees
8: ),
9: arg2
10: AS ( SELECT FirstName ,
11: LastName ,
12: BirthDate ,
13: DATEADD(YEAR, DIFF, BirthDate) AS CurrentBirthday ,
14: DATEADD(YEAR, DIFF + 1, BirthDate) AS NextBirthday ,
15: Today
16: FROM arg1
17: ),
18: arg3
19: AS ( SELECT FirstName ,
20: LastName ,
21: BirthDate ,
22: CurrentBirthday
23: + CASE WHEN DAY(BirthDate) = 29
24: AND DAY(CurrentBirthday) = 28 THEN 1
25: ELSE 0
26: END AS CurrentBirthday ,
27: NextBirthday
28: + CASE WHEN DAY(Birthdate) = 29
29: AND DAY(NextBirthday) = 28 THEN 1
30: ELSE 0
31: END AS NextBirthday ,
32: Today
33: FROM arg2
34: )
35: SELECT FirstName ,
36: LastName ,
37: BirthDate ,
38: CASE WHEN Today > CurrentBirthday THEN NextBirthday
39: ELSE CurrentBirthday
40: END AS Birthday
41: FROM arg3