Click here to Skip to main content
15,860,861 members
Articles / Desktop Programming / ATL
Technical Blog

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

Rate me:
Please Sign up or sign in to vote.
1.00/5 (1 vote)
12 Sep 2012CPOL 16.2K   1   3
To find out whose birthday is coming in a given number of days is very simple.

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.

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

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


Written By
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.

Comments and Discussions

 
QuestionToo complicated Pin
Gustav Brock17-Sep-12 2:29
professionalGustav Brock17-Sep-12 2:29 
SuggestionFormat Issue Pin
Sandip.Nascar12-Sep-12 7:08
Sandip.Nascar12-Sep-12 7:08 
GeneralRe: Format Issue Pin
_Amy12-Sep-12 7:26
professional_Amy12-Sep-12 7:26 
I agree. Smile | :)
Read the article "Table Valued Parameters".

--Amit

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

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