Birthday SQL Function

Josh Briggs from

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

Answers

answered 2 months ago sniperd #1

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

results:

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.

answered 2 months ago Wolfgang Kais #2

You can use the DATEADD and 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);