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

Comments

Popular posts from this blog

മലയാളത്തില്‍ മൊത്തം എത്ര അക്ഷരങ്ങള്‍ ഉണ്ട്?

Convert Number To Words in SQL Sever

Crystal reports load report failed: Could not load file or assembly CrystalDecisions.Web, Version=10.2.3600.0