December 27, 2011

How to get a list of upcoming birthdays in SQL Server


DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = DATEADD(DAY, -1, GETDATE())
SET @EndDate   = DATEADD(DAY, 10, GETDATE())


SELECT TOP (5) ID, Name, DOB
FROM Personal
WHERE (DATEADD(YEAR, DATEDIFF(YEAR,  DOB, @StartDate), dob) BETWEEN @StartDate AND @EndDate OR
DATEADD(YEAR, DATEDIFF(YEAR,  dob, @EndDate), dob) BETWEEN @StartDate AND @EndDate) AND
(Active = 'Y') AND (YEAR(DOB)>1900) AND ID NOT IN (1391,1001)
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)


Order By is required if the start date and end date spread across 2 different Years

No comments:

Post a Comment