Click here to Skip to main content
11,721,385 members (79,061 online)
Click here to Skip to main content

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

, 13 Sep 2012 CPOL 23.4K 11
Rate this:
Please Sign up or sign in to vote.
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:

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

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

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.

However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).

For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

You may also be interested in...

Comments and Discussions

 
QuestionGood work Pin
King Fisher31-Mar-15 0:02
professionalKing Fisher31-Mar-15 0:02 
AnswerRe: Good work Pin
Mika Wendelius9-Apr-15 18:41
mentorMika Wendelius9-Apr-15 18:41 
GeneralMy vote of 5 Pin
Member 1016856512-Aug-13 18:03
memberMember 1016856512-Aug-13 18:03 
GeneralMy vote of 5 Pin
hyperlinx10-Jun-13 21:20
memberhyperlinx10-Jun-13 21:20 
GeneralMy vote of 5 Pin
Espen Harlinn27-Nov-12 11:42
mvpEspen Harlinn27-Nov-12 11:42 
GeneralRe: My vote of 5 Pin
Mika Wendelius27-Nov-12 17:28
mvpMika Wendelius27-Nov-12 17:28 
GeneralRe: My vote of 5 Pin
Mika Wendelius27-Nov-12 17:55
mvpMika Wendelius27-Nov-12 17:55 
GeneralRe: My vote of 5 Pin
Espen Harlinn27-Nov-12 22:01
mvpEspen Harlinn27-Nov-12 22:01 
GeneralRe: My vote of 5 Pin
Mika Wendelius28-Nov-12 3:36
mvpMika Wendelius28-Nov-12 3:36 
GeneralRe: My vote of 5 Pin
Espen Harlinn28-Nov-12 3:38
mvpEspen Harlinn28-Nov-12 3:38 
QuestionGetDate also adds the time Pin
ednrg13-Sep-12 9:50
memberednrg13-Sep-12 9:50 
AnswerRe: GetDate also adds the time Pin
Mika Wendelius13-Sep-12 10:02
mvpMika Wendelius13-Sep-12 10:02 
GeneralRe: GetDate also adds the time Pin
ednrg13-Sep-12 10:20
memberednrg13-Sep-12 10:20 
GeneralRe: GetDate also adds the time Pin
Mika Wendelius13-Sep-12 10:31
mvpMika Wendelius13-Sep-12 10:31 
ednrg wrote:
Please disregard my (incorrect) suggestion

No worries at all Smile | :)

And as I wrote, you're correct that the GETDATE returns the time portion. At some point I'll update this article to explain the sitution more thoroughly.

Thanks for the feedback!
The need to optimize rises from a bad design.My articles[^]

GeneralRe: GetDate also adds the time Pin
ednrg14-Sep-12 6:01
memberednrg14-Sep-12 6:01 
GeneralRe: GetDate also adds the time Pin
ednrg14-Sep-12 6:07
memberednrg14-Sep-12 6:07 
GeneralRe: GetDate also adds the time Pin
Mika Wendelius14-Sep-12 7:40
mvpMika Wendelius14-Sep-12 7:40 

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 | Terms of Use | Mobile
Web02 | 2.8.150901.1 | Last Updated 13 Sep 2012
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid