Click here to Skip to main content
Click here to Skip to main content

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

By , 12 Sep 2012
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
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.
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionToo complicated PinmemberGustav Brock17-Sep-12 2:29 
SuggestionFormat Issue PinmemberSandip.Nascar12-Sep-12 7:08 
GeneralRe: Format Issue Pinmember_Amy12-Sep-12 7:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 12 Sep 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid