Birthday Query in MS SQL Server

Submitted by Sergey on March 22, 2009 - 10:10

It's a common request to get a list of people whose birthday (or an anniversary date) falls in the specified date range w/o regard to the year.

In the query below a birthday date is converted to the year of the range start and end dates, than both results are compared to the date range itself. In the process February 29 is gracefully converted to February 28 for a none leap years.

The query originally was published by Michael Levy on UT.

This is sample code. Add error handling and adjust to your requirements as necessary.

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)


Sample data for the Birthday query

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')
INSERT INTO People (FullName, dob) VALUES ('Sam Jobless', '1958-03-01')
INSERT INTO People (FullName, dob) VALUES ('Nancy Davolio', '1968-12-08')
INSERT INTO People (FullName, dob) VALUES ('Andrew Fuller', '1952-02-19')
INSERT INTO People (FullName, dob) VALUES ('Janet Leverling', '1963-08-30')
INSERT INTO People (FullName, dob) VALUES ('Margaret Peacock', '1958-09-19')
INSERT INTO People (FullName, dob) VALUES ('Steven Buchanan', '1955-03-04')
INSERT INTO People (FullName, dob) VALUES ('Michael Suyama', '1963-07-02')
INSERT INTO People (FullName, dob) VALUES ('Robert King', '1960-05-29')
INSERT INTO People (FullName, dob) VALUES ('Laura Callahan', '1958-01-09')
INSERT INTO People (FullName, dob) VALUES ('Anne Dodsworth', '1969-07-02')
INSERT INTO People (FullName, dob) VALUES ('Jack Lighthouse', NULL)
Your rating: None Average: 5 (2 votes)

Thank you! Saved me lots of

Submitted by Mike (not verified) on June 11, 2009 - 03:52.

Thank you! Saved me lots of headaches with this brilliantly simple trick to negate the year difference in the comparison.

New Year order

Submitted by Mike (not verified) on June 11, 2009 - 08:34.

Just a issue come across. After I select the birthdays between the dates I order by month, date:

SELECT Personal.DOB, Personal.LastName, Personal.FirstName 
	FROM Personal
	WHERE DATEADD(YEAR, DATEDIFF(YEAR, Personal.DOB, @StartDate), Personal.DOB) BETWEEN @StartDate AND @EndDate
		OR DATEADD(YEAR, DATEDIFF(YEAR, Personal.DOB, @EndDate), Personal.DOB) BETWEEN @StartDate AND @EndDate 
	ORDER BY DATEPART(MONTH, Personal.DOB), DATEPART(DAY, Personal.DOB)

The problem comes in when date range fall across a new year. The January birthdays are sorted before the December ones.
Any suggestions?

New Year order

Submitted by Sergey on June 11, 2009 - 09:27.

Hi Mike,

Following ORDER BY clause will respect the date range that crosses into new year

	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)