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

• 13^th September, 2012: Alternative created.