Birthday Query in MS SQL Server
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)
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
Thank you! Saved me lots of
New Year order
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
Following ORDER BY clause will respect the date range that crosses into new year
nice
Thanks
Great post. Really
Brilliant
Saved a lot of work in my case.
Thanks guys....
very usefull, thanks
Need one query between two months
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.
Re: Need one query between two months
The query already does what you want. You only have to calculate proper start and end dates.
Thank you !