SQL Server: Query to find upcoming birthdays for the current week

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.

--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)
            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.

-- 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
            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