Birthday Query in MS SQL Server

Submitted by Sergey on March 22, 2009 - 15:10 UTC
topic: 
sql
tsql
mssql
code
query
birthday

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.





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19






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 




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25






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)

Comments

Submitted by Mike on June 11, 2009 - 08:52 UTC
Thank you! Saved me lots of headaches with this brilliantly simple trick to negate the year difference in the comparison.

Submitted by Mike on June 11, 2009 - 13:34 UTC
Just a issue come across. After I select the birthdays between the dates I order by month, date: 




1
2
3
4
5
6





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?

Submitted by Sergey on June 11, 2009 - 14:27 UTC
Hi Mike,

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




1
2
3
4





	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)

Submitted by George on March 18, 2010 - 10:42 UTC
very good informative article..... students who learning sql will be satisfied with this query and article.... Keep Up.

Thanks

Submitted by Anonymous on August 19, 2012 - 17:57 UTC
Great post. Really helpful.thanks a lot.

Submitted by Juergen on October 31, 2012 - 13:24 UTC
Thanks for that brilliant statement.
Saved a lot of work in my case.

Submitted by Dhanushka on April 4, 2013 - 09:49 UTC
Thanks guys....

Submitted by Alex on February 24, 2014 - 13:13 UTC
very usefull, thanks

Submitted by vinay kumar on November 12, 2014 - 08:37 UTC
hi team,
can one help me writing the query for like ...
if we want the DOB of people in between two months like if we execute my query(eg on12th ,Nov) ,it should me give the last month(oct) 12th to current month (nov) 11th DOB names similarily if we execute my query(eg on12th ,Dec) ,it should me give the last month(Nov) 12th to current month (Dec) 11th DOB names irrespctive of the year.

Please help me in writng this query.

Regards,
Vinay.

Submitted by Sergey on November 12, 2014 - 14:30 UTC
Kumar,

The query already does what you want. You only have to calculate proper start and end dates.

Submitted by Gabriele on February 19, 2016 - 16:31 UTC
Thank you very much for this query, its has helped me to correct my query !