關於SQLServer2005的學習筆記——生日問題

--建立表和資料

CREATE TABLE employees

(

  name     VARCHAR(50),

  birthday DATETIME

)

INSERT INTO employees VALUES('WBQ','1948-12-08');

INSERT INTO employees VALUES('CZH','1952-02-19');

INSERT INTO employees VALUES('LB','1963-08-30');

INSERT INTO employees VALUES('YLL','1937-09-19');

INSERT INTO employees VALUES('YGQ','1955-03-04');

INSERT INTO employees VALUES('CHH','1963-07-02');

INSERT INTO employees VALUES('SWG','1960-05-29');

INSERT INTO employees VALUES('HW','1958-01-01');

INSERT INTO employees VALUES('YY','1972-02-29');

INSERT INTO employees VALUES('LM','1999-02-08');

INSERT INTO employees VALUES('ZY','1972-06-09');

INSERT INTO employees VALUES('WZH','1999-02-28');

SELECT name,birthday FROM employees

--普通的SQL實現

SELECT

  name,

  birthday,

  GETDATE() getdate,

  CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar,

  CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) todayDateTime,

  DATEDIFF(YY,birthday,GETDATE()) DateBetween,

  DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) ThisBirthday,

  DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday) NextBirthday,

  CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)

       THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday)

       ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)

  END newBirthdayStandard,

  CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)       

       THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday)+

            CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday))=28 THEN 1 ELSE 0 END

       ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) +

            CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday))=28 THEN 1 ELSE 0 END

  END newBirthdayForeign 

FROM employees

--使用函式來實現

CREATE FUNCTION GetBirthday(@birthday DATETIME,@flag INT)

RETURNS DATETIME

AS

BEGIN

  DECLARE @BirthdayRet DATETIME,@BirthdayThis DATETIME,@BirthdayNext DATETIME,@today DATETIME,@dateBetween INT

  SET @today=CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))

  SET @dateBetween=DATEDIFF(YY,@birthday,GETDATE())

  SET @BirthdayThis=DATEADD(YY,@DateBetween,@birthday)    

  SET @BirthdayNext=DATEADD(YY,@DateBetween+1,@birthday)

  IF @flag=1 --2月29日的生日計算為2月28日

  BEGIN

    IF @today>@BirthdayThis

       SET @BirthdayRet=@BirthdayNext

    ELSE

       SET @BirthdayRet=@BirthdayThis

  END

  ELSE       --2月29日的生日計算為3月1日

  BEGIN

    IF @today>@BirthdayThis

            IF DAY(@birthday)=29 AND DAY(@BirthdayNext)=28

          SET @BirthdayRet=@BirthdayNext+1

       ELSE

          SET @BirthdayRet=@BirthdayNext

    ELSE

            IF DAY(@birthday)=29 AND DAY(@BirthdayThis)=28

          SET @BirthdayRet=@BirthdayThis+1

       ELSE

          SET @BirthdayRet=@BirthdayThis  END    

  RETURN @BirthdayRet

END

SELECT name,birthday,dbo.GetBirthday(birthday,0),dbo.GetBirthday(birthday,1) FROM employees

--透過CTE來實現

WITH DateBetween AS

(

  SELECT  

    name,

    birthday,

    GETDATE() getdate,

    CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar,

    CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) todayDateTime,

    DATEDIFF(YY,birthday,GETDATE()) DateBetween

  FROM employees

),

DateBirthdayThisAndNext AS

(

  SELECT

    name,birthday,getdate,todaydatetime,datebetween,

    DATEADD(YY,DateBetween,birthday) AS DateCur,

    DATEADD(YY,DateBetween+1,birthday) AS DateNext

  FROM DateBetween

),

DateBirthdayThisAndNextForeign AS

(

  SELECT

    name,birthday,todaydatetime,

    DateCur,DateNext,

    DateCur+CASE WHEN DAY(birthday)=29 AND DAY(DateCur)=28 THEN 1 ELSE 0 END AS DateCurForeign,

    DateNext+CASE WHEN DAY(birthday)=29 AND DAY(DateNext)=28 THEN 1 ELSE 0 END AS DateNextForeign

  FROM DateBirthdayThisAndNext

),

DateBirthday AS

(

  SELECT

    name,birthday,

    CASE WHEN DateCurForeign>=todaydatetime THEN DateCurForeign ELSE DateNextForeign END AS birthDayForeign,

    CASE WHEN DateCur>=todaydatetime THEN DateCur ELSE DateNext END AS birthDayStandard

  FROM DateBirthdayThisAndNextForeign

)

SELECT name,birthday,birthDayForeign,birthDayStandard FROM DateBirthday