SQL Server: Query to Find Upcoming birthdays for Current Week

 A common query, for Human Resource databases or different social sites is to find out employee/subscribers name who’s birthday is coming in near future i.e. (In current week, or in next given days).

To find out, whose birthday is coming in given number of days is bit simple.

--Create table variable to hold our test records

DECLARE  @Workers  TABLE (WorderName VARCHAR(50), DOB DATETIME)

--Insert test records

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'

--Variable to provide requried number of days

DECLARE @InNextDays INT

SET @InNextDays = 3       

-- Query to find workers, whose birthday is in given number of days

SELECT  *

FROM    @Workers e

WHERE   1 =

CASE WHEN MONTH(GETDATE()) < MONTH(GETDATE() + @InNextDays)

     THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE() + @InNextDays)

            AND DAY(DOB) BETWEEN DAY(DATEADD(s, -1,

                                    DATEADD(mm, DATEDIFF(m, 0,

                                    GETDATE()) + 1, 0) + 1))

                     AND     DAY(GETDATE()

                                    + @InNextDays) THEN 1

               WHEN MONTH(DOB) = MONTH(GETDATE())

                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1

                                 AND     DAY(GETDATE())

                                         + @InNextDays THEN 1

               ELSE 0

          END

     ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())

                    AND DAY(DOB) BETWEEN DAY(GETDATE()) + 1

                                 AND     DAY(GETDATE())

                                         + @InNextDays THEN 1

               ELSE 0

          END

END

 

And following query will help you to find out workers with birthday in current week.

-- Query to find workers, whose birthday is in 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

            AND DAY(DOB) >= 1

            AND DAY(DOB) < DAY(DATEADD(WK,

                                 DATEDIFF(WK, 0, GETDATE())

                                       + 1, -1)) THEN 1

    WHEN MONTH(DOB) = MONTH(GETDATE())

            AND DAY(DOB) >= DAY(GETDATE())

            AND DAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm,

                                                            DATEDIFF(m,0,GETDATE()),0))) THEN 1

     

       ELSE 0 END

 

ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())

            AND DAY(DOB) >= DAY(GETDATE())+1

            AND DAY(DOB) < DAY(DATEADD(WK,

                                 DATEDIFF(WK, 0, GETDATE())

                                       + 1, -1)) THEN 1

       ELSE 0

  END

END