Reply to comment

Birthday Query in MS SQL Server

Submitted by Sergey on March 22, 2009 - 11: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)

The Bereznikers

Tags

Recent posts

Recent comments

Donate

Reply to comment

Birthday Query in MS SQL Server

Reply