根据一个人的出生日期和今天的日期，计算他最近的生日（使用Northwind数据库的Employees表）...

使用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

转载于:https://www.cnblogs.com/Junelee1211/archive/2011/08/11/2135349.html