问题出自 sqlserver2005 技术内幕 t-sql程序设计使用北方数据库 的 employees表它首先插入 2条数据,生在2月29答案是-- Add two employees
SET NOCOUNT ON;
USE Northwind;INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
VALUES('Leaping', 'George', '19720229');
INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
VALUES('Today', 'Mary', CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME));WITH Args1 AS
(
SELECT LastName, FirstName, BirthDate,
DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
FROM dbo.Employees
),
Args2 AS
(
SELECT LastName, FirstName, BirthDate, Today,
DATEADD(year, Diff, BirthDate) AS BDCur,
DATEADD(year, Diff + 1, BirthDate) AS BDNxt
FROM Args1
),
Args3 AS
(
SELECT LastName, FirstName, BirthDate, Today,
BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
THEN 1 ELSE 0 END AS BDCur,
BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
THEN 1 ELSE 0 END AS BDNxt
FROM Args2
)
SELECT LastName, FirstName, BirthDate,
CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
FROM Args3;
args1 用于计算没味员工的出生日期和今天相差的年数,以及今天午夜的日期。要计算某员工最近的生日,需要把birthday加上diff列的年数。如果结果早于今天,需要再加一年。
--书中的话
偶觉得好复杂, 不知道各位大侠也没有简单一点的方法呢,学习~~
declare @bri datetime
set @bri='1984-12-10'
declare @dt1 datetime,@dt2 datetime,@dt3 datetime
set @dt1=datename(yy,dateadd(yy,-1,getdate()))+'-'+datename(mm,@bri)+'-'+datename(dd,@bri)
set @dt2=datename(yy,getdate())+'-'+datename(mm,@bri)+'-'+datename(dd,@bri)
set @dt3=datename(yy,dateadd(yy,1,getdate()))+'-'+datename(mm,@bri)+'-'+datename(dd,@bri)
select 最近的生日=case
when datediff(dd,getdate(),@dt1)<datediff(dd,getdate(),@dt2) and datediff(dd,getdate(),@dt1)<datediff(dd,getdate(),@dt2) then @dt1
when datediff(dd,getdate(),@dt2)<datediff(dd,getdate(),@dt1) and datediff(dd,getdate(),@dt2)<datediff(dd,getdate(),@dt3) then @dt2
when datediff(dd,getdate(),@dt3)<datediff(dd,getdate(),@dt1) and datediff(dd,getdate(),@dt3)<datediff(dd,getdate(),@dt2) then @dt3 end