linux计算生日时间,生日计算

/*

--Desc: 在计算员工当前日期的生日时，需要考虑闰年的问题，

一般在2月29号出生的人在非闰年时生日在3月1号过。

--Environment:  SQL SERVER 2005

--Author: Learnsql

*/

USE  Tempdb;

GO

--创建测试表

CREATE TABLE #Employee

(

[Name] NVARCHAR(10),

BirthDay DATETIME

);

GO

--插入数据

INSERT INTO #Employee([Name],BirthDay) VALUES('Tom','1970-1-3');

INSERT INTO #Employee([Name],BirthDay) VALUES('Jim','1971-11-23');

INSERT INTO #Employee([Name],BirthDay) VALUES('Lucy','1972-2-29');

INSERT INTO #Employee([Name],BirthDay) VALUES('Bob','1979-3-15');

INSERT INTO #Employee([Name],BirthDay) VALUES('Jack','1985-5-10');

GO

--生日计算

WITH Emp1([Name],BirthDay,DiffYear,Today) AS --计算当前日期和生日相差的年数

(

SELECT [Name], BirthDay,

DATEDIFF(YEAR, BirthDay, GETDATE()) AS DiffYear,

CAST(CONVERT(NVARCHAR(8), GETDATE(), 112) AS DATETIME) AS Today

FROM #Employee

),

Emp2([Name],BirthDay,Today,BDCur,BDNxt) AS  --计算当前生日及下一年生日

(

SELECT [Name],BirthDay, Today,

DATEADD(YEAR, DiffYear, BirthDay) AS BDCur,

DATEADD(YEAR, DiffYear + 1, BirthDay) AS BDNxt

FROM Emp1

),

Emp3([Name],BirthDay,Today,BDCur,BDNxt) AS  --对闰年生日处理

(

SELECT [Name],BirthDay, Today,

BDCur + CASE WHEN DAY(BirthDay) = 29 AND DAY(BDCur) = 28

THEN 1 ELSE 0 END AS BDCur,

BDNxt + CASE WHEN DAY(BirthDay) = 29 AND DAY(BDNxt) = 28

THEN 1 ELSE 0 END AS BDNxt

FROM Emp2

)

SELECT [Name], BirthDay,

CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDate,

DATEDIFF(DAY,Today,CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END) AS DiffDay

FROM Emp3;