Click here to Skip to main content
Click here to Skip to main content
Alternative Article

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

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

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)

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.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 1016856512-Aug-13 18:03 
GeneralMy vote of 5 Pinmemberhyperlinx10-Jun-13 21:20 
GeneralMy vote of 5 PinmvpEspen Harlinn27-Nov-12 11:42 
GeneralRe: My vote of 5 PinmvpMika Wendelius27-Nov-12 17:28 
GeneralRe: My vote of 5 PinmvpMika Wendelius27-Nov-12 17:55 
GeneralRe: My vote of 5 PinmvpEspen Harlinn27-Nov-12 22:01 
GeneralRe: My vote of 5 PinmvpMika Wendelius28-Nov-12 3:36 
GeneralRe: My vote of 5 PinmvpEspen Harlinn28-Nov-12 3:38 
QuestionGetDate also adds the time Pinmemberednrg13-Sep-12 9:50 
AnswerRe: GetDate also adds the time PinmvpMika Wendelius13-Sep-12 10:02 
It's true that GETDATE also returns the time portion, but I don't quite understand your suggestion since in the comparison everything is already converted to date.
 
Could you point out the specific place where the conversion is missing?
 
If you're thinking about the first line of the WHERE clause, it's true that it'll have a time portion but it's always midnight and will match with a date (of course as long as the date is the same).
The need to optimize rises from a bad design.My articles[^]

GeneralRe: GetDate also adds the time Pinmemberednrg13-Sep-12 10:20 
GeneralRe: GetDate also adds the time PinmvpMika Wendelius13-Sep-12 10:31 
GeneralRe: GetDate also adds the time Pinmemberednrg14-Sep-12 6:01 
GeneralRe: GetDate also adds the time Pinmemberednrg14-Sep-12 6:07 
GeneralRe: GetDate also adds the time PinmvpMika 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 | Mobile
Web02 | 2.8.140415.2 | Last Updated 13 Sep 2012
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid