gpt4 book ai didi

sql-server - SQL 服务器 : birthdays on Leap Year

转载 作者:行者123 更新时间:2023-12-01 06:20:10 25 4
gpt4 key购买 nike

我有一张员工生日表。我正在尝试创建一个存储过程,在 2 个给定日期内返回每个人的生日。我们有闰年出生的员工。

按照 http://www.berezniker.com/content/pages/sql/microsoft-sql-server/birthday-query-ms-sql-server 的示例,当某人的生日落在闰年时,我可以成功地返回一个人

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.

另外,如果你有心情,我的全部意图如下:

我想传递 2 个日期,无论相隔多远:@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

最佳答案

这是一种方法:

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 ║
╚════════════╩═════════════════════════╩═════════════════════════╝

关于sql-server - SQL 服务器 : birthdays on Leap Year,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17372336/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com