A common query for Human Resource databases or different social sites is to find out employee/subscribers name whose birthday comes in
the near future (in the current week, or in next few days). To find out whose birthday is coming in
a given number of days is very simple.
DECLARE @Workers TABLE (WorderName VARCHAR(50), DOB DATETIME)
INSERT INTO @Workers
SELECT 'Ryan','1972-08-24 00:00:00' UNION ALL
SELECT 'James','1985-09-26 00:00:00' UNION ALL
SELECT 'Jasson','1983-08-25 00:00:00' UNION ALL
SELECT 'Tara','1991-09-24 00:00:00' UNION ALL
SELECT 'William','1992-08-19 00:00:00' UNION ALL
SELECT 'Judy','1989-09-23 00:00:00'
DECLARE @InNextDays INT
SET @InNextDays = 3
SELECT *
FROM @Workers e
WHERE 1 =
CASE WHEN MONTH(GETDATE()) < MONTH(GETDATE() + @InNextDays)
THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE() + @InNextDays)
ANDDAY(DOB) BETWEEN DAY(DATEADD(s, -1,
DATEADD(mm, DATEDIFF(m, 0,
GETDATE()) + 1, 0) + 1))
AND DAY(GETDATE()
+ @InNextDays) THEN 1
WHENMONTH(DOB) = MONTH(GETDATE())
ANDDAY(DOB) BETWEEN DAY(GETDATE()) + 1
AND DAY(GETDATE())
+ @InNextDays THEN 1
ELSE0
END
ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
ANDDAY(DOB) BETWEEN DAY(GETDATE()) + 1
AND DAY(GETDATE())
+ @InNextDays THEN 1
ELSE0
END
END
And the following query will help you find out workers with birthday in the current week.
SELECT *
FROM @Workers e
WHERE 1 = CASE WHEN MONTH(GETDATE()) < MONTH(DATEADD(WK,
DATEDIFF(WK, 0, GETDATE())+1,-1))
THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE()) + 1
ANDDAY(DOB) >= 1
ANDDAY(DOB) < DAY(DATEADD(WK,
DATEDIFF(WK, 0, GETDATE())
+ 1, -1)) THEN 1
WHEN MONTH(DOB) = MONTH(GETDATE())
ANDDAY(DOB) >= DAY(GETDATE())
ANDDAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) THEN 1
ELSE 0 END
ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
ANDDAY(DOB) >= DAY(GETDATE())+1
ANDDAY(DOB) < DAY(DATEADD(WK,
DATEDIFF(WK, 0, GETDATE())
+ 1, -1)) THEN 1
ELSE 0
END
END
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.