SQL Server：闰年生日问题

Question

我有一个员工生日的表格。我想创建一个存储过程，返回在给定日期范围内所有人的生日。我们有在闰年出生的员工。

我可以成功地按照这个例子查询到在闰年出生的员工生日。

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = '2009-02-22'
SET @EndDate   = '2009-02-28'

--SET @StartDate = '2008-02-22'
--SET @EndDate   = '2008-02-29'

SELECT 
  FullName, 
  DATEPART(MONTH, dob) AS MONTH, 
  DATEPART(DAY, dob) AS DAY, 
  CONVERT(VARCHAR(10), dob, 111) AS dob
FROM 
  People
WHERE  
  DATEADD(YEAR, DATEDIFF(YEAR,  dob, @StartDate), dob) BETWEEN @StartDate AND @EndDate
OR 
  DATEADD(YEAR, DATEDIFF(YEAR,  dob, @EndDate), dob) BETWEEN @StartDate AND @EndDate
ORDER BY 
  CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR,  dob, @StartDate), dob) 
  BETWEEN @StartDate AND @EndDate THEN 1 ELSE 2 END, 
  DATEPART(MONTH, dob), DATEPART(DAY, dob)


CREATE TABLE People 
    (
        PK INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        FullName VARCHAR(30) NOT NULL,
        dob DATETIME NULL
    )
GO

INSERT INTO People (FullName, dob) VALUES ('John Smith', '1965-02-28')
INSERT INTO People (FullName, dob) VALUES ('Alex Black', '1960-02-29')
INSERT INTO People (FullName, dob) VALUES ('Bill Doors', '1968-02-27')
...
--shortened for clarity

然而，根据上述数据，我的目标是显示 Alex Black 在 2014 年的生日为 2/28/2014，在 2016 年的生日为 2/29/2016。

另外，如果您有兴趣，我的完整意图如下：

我想传递两个日期，无论相隔多远：@DateFrom date = '1/1/2014' 和 @DateTo date = '12/31/2016'。我希望返回的结果是：

FULLNAME        DOB
Bill Doors      2014-02-27
John Smith      2014-02-28
Alex Black      2014-02-28
Bill Doors      2015-02-27
John Smith      2015-02-28
Alex Black      2015-02-28
Bill Doors      2016-02-27
John Smith      2016-02-28
Alex Black      2016-02-29 -- note this year the date is feb 29th

- RoLYroLLs

Answer 1

这里有一种方法：

DECLARE @StartDate DATETIME, @EndDate DATETIME, @I INT

SET @StartDate = '20140101'
SET @EndDate   = '20161231'
SET @I = 0

DECLARE @Years TABLE(Years DATE)


WHILE @I <= DATEDIFF(YEAR,@StartDate,@EndDate)
BEGIN
    INSERT INTO @Years
    SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,@I,@StartDate)),0)

    SET @I = @I + 1
END

SELECT  B.FullName,
        B.dob,
        DATEADD(YEAR,DATEDIFF(YEAR,dob,Years),dob) BirthDay
FROM @Years A
CROSS JOIN People B
WHERE DATEADD(YEAR,DATEDIFF(YEAR,dob,Years),dob) >= @StartDate
AND DATEADD(YEAR,DATEDIFF(YEAR,dob,Years),dob) <= @EndDate

当然，您不需要每次都创建那个@Years表，我建议您创建一个包含此信息的日历表。

结果：

╔════════════╦═════════════════════════╦═════════════════════════╗
║  FullName  ║           dob           ║        BirthDay         ║
╠════════════╬═════════════════════════╬═════════════════════════╣
║ John Smith ║ 1965-02-28 00:00:00.000 ║ 2014-02-28 00:00:00.000 ║
║ Alex Black ║ 1960-02-29 00:00:00.000 ║ 2014-02-28 00:00:00.000 ║
║ Bill Doors ║ 1968-02-27 00:00:00.000 ║ 2014-02-27 00:00:00.000 ║
║ John Smith ║ 1965-02-28 00:00:00.000 ║ 2015-02-28 00:00:00.000 ║
║ Alex Black ║ 1960-02-29 00:00:00.000 ║ 2015-02-28 00:00:00.000 ║
║ Bill Doors ║ 1968-02-27 00:00:00.000 ║ 2015-02-27 00:00:00.000 ║
║ John Smith ║ 1965-02-28 00:00:00.000 ║ 2016-02-28 00:00:00.000 ║
║ Alex Black ║ 1960-02-29 00:00:00.000 ║ 2016-02-29 00:00:00.000 ║
║ Bill Doors ║ 1968-02-27 00:00:00.000 ║ 2016-02-27 00:00:00.000 ║
╚════════════╩═════════════════════════╩═════════════════════════╝

- Lamak

---

+1个好的计算生日的方法，我稍微修改了我的答案，如果你不介意的话。 - Roman Pekar

+1 做得好！我更喜欢像 @RomanPekar 描述的 CTE 方法。 - RoLYroLLs

Answer 2

你可以尝试这个

declare
  @DateFrom date = '20140101',
  @DateTo date = '20161231'

;with
-- All years between @DateFrom and @DateTo
CTE_Years as (
  select datepart(yy, @DateFrom) as y
  union all
  select y + 1 as y
  from CTE_Years
  where y < datepart(yy, @DateTo)
), 
-- Calculate leap years
CTE_Years2 as (
  select
    cast(y as nvarchar(4)) as y,
    case
      when y / 400 * 400 = y then 1
      when y / 100 * 100 = y then 0
      when y / 4 * 4 = y then 1
      else 0
    end as Is_Leap_Year
  from CTE_Years
),
-- get peoples birth day and month in form 'mmdd'
CTE_People as (
  select
    FullName,
    right(convert(nvarchar(8), dob, 112), 4) as dob
  from People
),
-- get peoples birth date in given years
CTE_DOB as (
  select
    P.FullName,
    convert(
      date,
      Y.y + 
      case
        when Y.Is_Leap_Year = 0 and P.dob = '0229' then '0228'
        else P.dob
      end,
      112
    ) as dob
  from CTE_Years2 as Y
    cross join CTE_People as P
)
-- Final query
select *
from CTE_DOB
where dob > @DateFrom and dob < @DateTo
order by DOB asc

请查看SQL FIDDLE示例

编辑: Lamak提醒我一种很好的计算生日的方法，因此这是修改后的版本

declare
  @DateFrom date = '1/1/2014',
  @DateTo date = '12/31/2016'

;with
CTE_Years as (
  select dateadd(yy, datediff(yy, 0, @DateFrom), 0) as y
  union all
  select dateadd(yy, 1, y) as y
  from CTE_Years
  where y < @DateTo
), 
CTE_DOB as (
  select
    P.FullName,
    dateadd(yy, datediff(yy, P.dob, Y.y), P.dob) as dob
  from CTE_Years as Y
      cross join People as P
)
select *
from CTE_DOB
where dob > @DateFrom and dob < @DateTo
order by DOB asc

SQL FIDDLE EXAMPLE

- Roman Pekar

---

谢谢@RomanPekar。非常好用，喜欢这些示例！举个例子，如果我们的数据库中有100,000,000+人，并且我们正在搜索一个较小的时间范围，比如1个月，那么WHERE子句是否不应该放在CTE_DOB内部，以便我们不会跨越每一行进行交叉连接？或者有更好的方法吗？ - RoLYroLLs

无论如何，它都将是完全扫描，除非您创建一个特殊的字段，例如月份，并在t上创建索引。 - Roman Pekar

Answer 3

DECLARE @bd DATE = '1960-02-29';

WITH years
     AS ( SELECT
            *
          FROM   (VALUES (2013),
                         (2014),
                         (2015),
                         (2016)) AS x(y) )
SELECT
  y, DATEADD( year, y - DATEPART( year, @bd ), @bd )
FROM   years 

- Ben Thul