Click here to Skip to main content
15,867,141 members
Articles / Desktop Programming / ATL
Alternative
Article

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
13 Sep 2012CPOL1 min read 77.6K   11   21
This is an alternative for "SQL Server: Query to find upcoming birthdays for the current week"

Introduction

This is an alternative to the original tip 'SQL Server: Query to find upcoming birthdays for the current week' provided by aasim abdullah.

Changes to the original tip

There are few changes in this alternative compared to the original tip.

The first difference is that a date data type is used instead of datetime to store the date of birth (DOB). This makes it a bit easier to handle the dates and compare them to current date.

The first query, which retrieves the workers whose birthday is in given days, can also be formulated as the following:

SQL
--Create table variable to hold our test records
DECLARE @Workers TABLE (WorkerName VARCHAR(50), DOB DATE);

--Insert test records
INSERT INTO @Workers
SELECT 'Ryan',    '1972-08-24' UNION ALL
SELECT 'James',   '1985-09-26' UNION ALL
SELECT 'Jasson',  '1983-08-25' UNION ALL
SELECT 'Tara',    '1991-09-24' UNION ALL
SELECT 'William', '1992-08-19' UNION ALL
SELECT 'Judy',    '1989-09-15';

--Variable to provide required number of days
DECLARE @InNextDays INT;
SET @InNextDays = 3;

-- Query to find workers, whose birthday is in given number of days
SELECT *
FROM @Workers 
WHERE DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DOB), DOB)
      BETWEEN CONVERT( DATE, GETDATE()) 
      AND CONVERT( DATE, GETDATE() + @InNextDays);

The idea is, that the actual birthday is shifted to current year by adding the number of years between the year of DOB and the current year. After that, a simple date range comparison is used.

The second query which retrieves the workers, whose birthday is on current week can be written like:

SQL
-- Query to find workers, whose birthday is in current week
SELECT * 
FROM @Workers
WHERE DATEPART( Week, DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DOB), DOB))
      = DATEPART( Week, GETDATE());

The basic idea is the same as in the first query I explained. Only this time the week numbers are compared.

Note: In the example, the default week numbering is used so depending on the requirements, the query may need to be adjusted to use different week numbers (for example ISO week). For more information, refer to DATEPART.

History

  • 13th September, 2012: Alternative created.


License

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


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 1509548910-Mar-21 4:51
Member 1509548910-Mar-21 4:51 
SuggestionUpcoming Birthdays new procs Pin
sBudha6-Apr-17 23:34
sBudha6-Apr-17 23:34 
GeneralRe: Upcoming Birthdays new procs Pin
eyal gilboa11-May-17 22:37
eyal gilboa11-May-17 22:37 
QuestionGood work Pin
King Fisher31-Mar-15 0:02
professionalKing Fisher31-Mar-15 0:02 
AnswerRe: Good work Pin
Wendelius9-Apr-15 18:41
mentorWendelius9-Apr-15 18:41 
GeneralMy vote of 5 Pin
Member 1016856512-Aug-13 18:03
Member 1016856512-Aug-13 18:03 
GeneralMy vote of 5 Pin
ZeroDigitality10-Jun-13 21:20
ZeroDigitality10-Jun-13 21:20 
GeneralMy vote of 5 Pin
Espen Harlinn27-Nov-12 11:42
professionalEspen Harlinn27-Nov-12 11:42 
GeneralRe: My vote of 5 Pin
Wendelius27-Nov-12 17:28
mentorWendelius27-Nov-12 17:28 
GeneralRe: My vote of 5 Pin
Wendelius27-Nov-12 17:55
mentorWendelius27-Nov-12 17:55 
GeneralRe: My vote of 5 Pin
Espen Harlinn27-Nov-12 22:01
professionalEspen Harlinn27-Nov-12 22:01 
GeneralRe: My vote of 5 Pin
Wendelius28-Nov-12 3:36
mentorWendelius28-Nov-12 3:36 
GeneralRe: My vote of 5 Pin
Espen Harlinn28-Nov-12 3:38
professionalEspen Harlinn28-Nov-12 3:38 
QuestionGetDate also adds the time Pin
ednrg13-Sep-12 9:50
ednrg13-Sep-12 9:50 
AnswerRe: GetDate also adds the time Pin
Wendelius13-Sep-12 10:02
mentorWendelius13-Sep-12 10:02 
GeneralRe: GetDate also adds the time Pin
ednrg13-Sep-12 10:20
ednrg13-Sep-12 10:20 
GeneralRe: GetDate also adds the time Pin
Wendelius13-Sep-12 10:31
mentorWendelius13-Sep-12 10:31 
GeneralRe: GetDate also adds the time Pin
ednrg14-Sep-12 6:01
ednrg14-Sep-12 6:01 
GeneralRe: GetDate also adds the time Pin
ednrg14-Sep-12 6:07
ednrg14-Sep-12 6:07 
GeneralRe: GetDate also adds the time Pin
Wendelius14-Sep-12 7:40
mentorWendelius14-Sep-12 7:40 
ednrg wrote:
Thanks for the potentially useful article. 5 from me!!!


Thanks Smile | :)
The need to optimize rises from a bad design.My articles[^]

GeneralRe: GetDate also adds the time Pin
Paresh P Patel12-Sep-18 19:23
Paresh P Patel12-Sep-18 19:23 

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.