I am trying to create an expression named "Birthday" to use in a given filter that reads "Birthday is equal to @today" to return any birthdays in a given day. The only way to get the birthday is based on the Date of Birth field. Obviously the DOB YEAR differs from the current YEAR. I need help creating a SQL expression that recognizes somebody's birthday without taking the year into consideration.sql sql-server tsql datepart
This should do the trick:
DECLARE @THEDAY INT = 1 DECLARE @THEMONTH INT = 1 IF OBJECT_ID('BDAY_TABLE') IS NOT NULL DROP TABLE BDAY_TABLE CREATE TABLE BDAY_TABLE (ID INT, BDAY DATETIME) INSERT INTO BDAY_TABLE (ID, BDAY) VALUES (1, '1/1/2000'), (2, '2/10/2000'), (2, '1/1/2010'), (2, '10/30/2005') SELECT * FROM BDAY_TABLE SELECT * FROM BDAY_TABLE WHERE DATEPART(DAY, BDAY) = @THEDAY AND DATEPART(MONTH, BDAY) = @THEMONTH
1 2000-01-01 00:00:00.000 2 2010-01-01 00:00:00.000
Note the use of
DATEPART, that way we don't really care about the year.
You can use the
YEAR functions to calculate the Birthday in a given year. Supposed that you have a variable
@TODAY that holds the current date, you could use this as your WHERE clause to get only those people that celebrate their birthday today:
WHERE DATEADD(year, YEAR(@TODAY)-YEAR(DOB), DOB) = @TODAY
This will also overcome the leap-year problem that way that such people will be returned on March 1st in a non-leapyear.
But most likely you want to get informed on birthdays let's say 2 weeks ahead, so I suggest to indeed calculate the next Birthday in order to be able to use whatever criteria on that, for example:
DECLARE @TODAY datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0); SELECT LastName, FirstName, Email, DOB, Birthday, YEAR(Birthday)-YEAR(DOB) AS becoming FROM People CROSS APPLY ( VALUES ( CASE WHEN DATEADD(year, YEAR(@TODAY)-YEAR(DOB), DOB) < @TODAY THEN DATEADD(year, 1 + YEAR(@TODAY)-YEAR(DOB), DOB) ELSE DATEADD(year, YEAR(@TODAY)-YEAR(DOB), DOB) END) ) bd (Birthday) WHERE Birthday <= DATEADD(day, 14, @TODAY);