SQL 选择即将到来的生日
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/83531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select Upcoming Birthdays
提问by Crob
I'm trying to write a stored procedure to select employees who have birthdays that are upcoming.
我正在尝试编写一个存储过程来选择即将过生日的员工。
SELECT * FROM Employees WHERE Birthday > @Today AND Birthday < @Today + @NumDays
SELECT * FROM Employees WHERE Birthday > @Today AND Birthday < @Today + @NumDays
This will not work because the birth year is part of Birthday, so if my birthday was '09-18-1983' that will not fall between '09-18-2008' and '09-25-2008'.
这将不起作用，因为出生年份是生日的一部分，所以如果我的生日是“09-18-1983”，那么它不会落在“09-18-2008”和“09-25-2008”之间。
Is there a way to ignore the year portion of date fields and just compare month/days?
有没有办法忽略日期字段的年份部分而只比较月/日？
This will be run every monday morning to alert managers of birthdays upcoming, so it possibly will span new years.
这将在每个星期一早上运行，以提醒经理即将到来的生日，因此它可能会跨越新年。
Here is the working solution that I ended up creating, thanks Kogus.
这是我最终创建的工作解决方案，感谢 Kogus。
SELECT * FROM Employees
WHERE Cast(DATEDIFF(dd, birthdt, getDate()) / 365.25 as int)
- Cast(DATEDIFF(dd, birthdt, futureDate) / 365.25 as int)
<> 0
回答by JosephStyons
Note: I've edited this to fix what I believe was a significant bug. The currently posted version works for me.
注意：我对此进行了编辑以修复我认为的一个重大错误。当前发布的版本对我有用。
This should work after you modify the field and table names to correspond to your database.
这应该在您修改字段和表名称以对应于您的数据库后工作。
SELECT
BRTHDATE AS BIRTHDAY
,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25) AS AGE_NOW
,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM
"Database name".dbo.EMPLOYEES EMP
WHERE 1 = (FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25))
-
(FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25))
Basically, it gets the # of days from their birthday to now, and divides that by 365 (to avoid rounding issues that come up when you convert directly to years).
基本上，它获取从他们生日到现在的天数，并将其除以 365（以避免直接转换为年份时出现的四舍五入问题）。
Then it gets the # of days from their birthday to a week from now, and divides that by 365 to get their age a week from now.
然后它得到从他们生日到一周后的天数，然后除以 365 得到他们一周后的年龄。
If their birthday is within a week, then the difference between those two values will be 1. So it returns all of those records.
如果他们的生日在一周内，那么这两个值之间的差值为 1。因此它返回所有这些记录。
回答by Andres SK
In case someone is still looking for a solution in MySQL(slightly different commands), here's the query:
如果有人仍在寻找MySQL 中的解决方案（命令略有不同），这里是查询：
SELECT
name,birthday,
FLOOR(DATEDIFF(DATE(NOW()),birthday) / 365.25) AS age_now,
FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),birthday) / 365.25) AS age_future
FROM user
WHERE 1 = (FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 30 DAY),birthday) / 365.25)) - (FLOOR(DATEDIFF(DATE(NOW()),birthday) / 365.25))
ORDER BY MONTH(birthday),DAY(birthday)
回答by Philippe Grondier
Best use of datediff and dateadd. No rounding, no approximates, no 29th of february bug, nothing but date functions
最好使用 datediff 和 dateadd。没有四舍五入，没有近似值，没有 2 月 29 日的错误，只有日期函数
ageOfThePerson = DATEDIFF(yyyy,dateOfBirth, GETDATE())dateOfNextBirthday = DATEADD(yyyy,ageOfThePerson + 1, dateOfBirth)daysBeforeBirthday = DATEDIFF(d,GETDATE(), dateofNextBirthday)
ageOfThePerson = DATEDIFF(yyyy,dateOfBirth, GETDATE())dateOfNextBirthday = DATEADD(yyyy,ageOfThePerson + 1, dateOfBirth)daysBeforeBirthday = DATEDIFF(d,GETDATE(), dateofNextBirthday)
Thanks to @Gustavo Cardoso, new definition for the age of the person
感谢@Gustavo Cardoso，人的年龄的新定义
ageOfThePerson = FLOOR(DATEDIFF(d,dateOfBirth, GETDATE())/365.25)
ageOfThePerson = FLOOR(DATEDIFF(d,dateOfBirth, GETDATE())/365.25)
回答by Edyn
Liked the approach of @strelc, but his sql was a bit off. Here's an updated version that works well and is simple to use:
喜欢@strelc 的方法，但他的 sql 有点不对劲。这是一个运行良好且易于使用的更新版本：
SELECT * FROM User
WHERE (DATEDIFF(dd, getdate(), DATEADD(yyyy,
DATEDIFF(yyyy, birthdate, getdate()) + 1, birthdate)) + 1) % 366 <= <number of days>
edit 10/2017: add single day to end
编辑 10/2017：添加单日结束
回答by Jovial
I found the solution for this. This may save someone's precious time.
我找到了解决方案。这可能会节省某人的宝贵时间。
select EmployeeID,DOB,dates.date from emp_tb_eob_employeepersonal
cross join dbo.GetDays(Getdate(),Getdate()+7) as dates where weekofmonthnumber>0
and month(dates.date)=month(DOB) and day(dates.date)=day(DOB)
GO
/****** Object: UserDefinedFunction [dbo].[GetDays] Script Date: 11/30/2011 13:19:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--SELECT [dbo].[GetDays] ('02/01/2011','02/28/2011')
ALTER FUNCTION [dbo].[GetDays](@startDate datetime, @endDate datetime)
RETURNS @retValue TABLE
(Days int ,Date datetime, WeekOfMonthNumber int, WeekOfMonthDescription varchar(10), DayName varchar(10))
AS
BEGIN
DECLARE @nextDay int
DECLARE @nextDate datetime
DECLARE @WeekOfMonthNum int
DECLARE @WeekOfMonthDes varchar(10)
DECLARE @DayName varchar(10)
SELECT @nextDate = @startDate, @WeekOfMonthNum = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH,0,@startDate),0),@startDate) + 1,
@WeekOfMonthDes = CASE @WeekOfMonthNum
WHEN '1' THEN 'First'
WHEN '2' THEN 'Second'
WHEN '3' THEN 'Third'
WHEN '4' THEN 'Fourth'
WHEN '5' THEN 'Fifth'
WHEN '6' THEN 'Sixth'
END,
@DayName
= DATENAME(weekday, @startDate)
SET @nextDay=1
WHILE @nextDate <= @endDate
BEGIN
INSERT INTO @retValue values (@nextDay,@nextDate, @WeekOfMonthNum, @WeekOfMonthDes, @DayName)
SELECT @nextDay=@nextDay + 1
SELECT @nextDate = DATEADD(day,1,@nextDate),
@WeekOfMonthNum
= DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH,0, @nextDate),0), @nextDate) + 1,
@WeekOfMonthDes
= CASE @WeekOfMonthNum
WHEN '1' THEN 'First'
WHEN '2' THEN 'Second'
WHEN '3' THEN 'Third'
WHEN '4' THEN 'Fourth'
WHEN '5' THEN 'Fifth'
WHEN '6' THEN 'Sixth'
END,
@DayName
= DATENAME(weekday, @nextDate)
CONTINUE
END
WHILE(@nextDay <=31)
BEGIN
INSERT INTO @retValue values (@nextDay,@nextDate, 0, '', '')
SELECT @nextDay=@nextDay + 1
END
RETURN
END
Make a cross join with the dates and check for the comparison of month and dates.
与日期交叉连接并检查月份和日期的比较。
回答by Fede H
My guess is using "365.25" soon or later would be fail.
我的猜测是使用“365.25”迟早会失败。
So I test the working solution using "365.25" And It don't return the same numbers of rows for every case. Here an example:
因此，我使用“365.25”测试了工作解决方案，并且它不会为每种情况返回相同数量的行。这里有一个例子：
http://sqlfiddle.com/#!3/94c3ce/7
http://sqlfiddle.com/#!3/94c3ce/7
test with year 2016 and 2116 and you will see the difference. I only can post one link but change de /7 by /8 to see both queries. (/10 and /11 for the first answer)
用 2016 年和 2116 年进行测试，您会看到差异。我只能发布一个链接，但将 de /7 更改为 /8 以查看两个查询。（/10 和 /11 为第一个答案）
So, I suggest this another query, where the point is determinate next birthday from a starting date and then compare if it is in my range of interest.
所以，我建议另一个查询，其中的重点是从开始日期确定下一个生日，然后比较它是否在我感兴趣的范围内。
SELECT * FROM Employees
WHERE
CASE WHEN (DATEADD(yyyy,DATEDIFF(yyyy, birthdt, @fromDate),birthdt) < @fromDate )
THEN DATEADD(yyyy,DATEDIFF(yyyy, birthdt, @fromDate)+1,birthdt)
ELSE DATEADD(yyyy,DATEDIFF(yyyy, birthdt, @fromDate),birthdt) END
BETWEEN @fromDate AND @toDate
回答by Nick Berardi
Sorry didn't see the requirement to neutralize the year.
抱歉，没有看到中和年份的要求。
select * from Employees
where DATEADD (year, DatePart(year, getdate()) - DatePart(year, Birthday), Birthday)
between convert(datetime, getdate(), 101)
and convert(datetime, DateAdd(day, 5, getdate()), 101)
This should work.
这应该有效。
回答by Dave Webb
You could use the DAYOFYEARfunction but be careful when you want to look for January birthdays in December. I think you'll be fine as long as the date range you're looking for doesn't span the New Year.
您可以使用该DAYOFYEAR功能，但要在 12 月查找 1 月生日时要小心。我认为只要您要查找的日期范围不跨越新年，就可以了。
回答by strelc
This is solution for MS SQL Server: It returns employees with birthdays in 30 days.
这是 MS SQL Server 的解决方案：它在 30 天内返回员工的生日。
SELECT * FROM rojstni_dnevi
WHERE (DATEDIFF (dd,
getdate(),
DATEADD ( yyyy,
DATEDIFF(yyyy, rDan, getdate()),
rDan)
nex )
+365) % 365 < 30
回答by Danilo
In less than a month:
不到一个月：
SELECT * FROM people WHERE MOD( DATEDIFF( CURDATE( ) , `date_birth`) /30, 12 ) <1 and (((month(`date_birth`)) = (month(curdate())) and (day(`date_birth`)) > (day (curdate() ))) or ((month(`date_birth`)) > (month(curdate())) and (day(`date_birth`)) < (day (curdate() ))))
