Select users with birthdate in the next 10 days

OriginalGriff

0.00/5 (No votes)

26 Dec 2019CPOL

4.2K

Select users with a birthday in the next n days is pretty easy, if you use the day of year. The only slight complication is the need to wrap round the year end.

Introduction

This started as a solution to a QA question, and it got me thinking - it's something people need to do, but "date of birth" is very different to "birthday" - it's likely to be several decades different, and we still need to consider December / January.

Using the code

SQL

DECLARE @TODAY INT = datepart(dayofyear, GETDATE())
SELECT DOB
FROM Student
WHERE (CASE WHEN datepart(dayofyear, DOB) < @TODAY 
            THEN datepart(dayofyear, DOB) + 365 
            ELSE datepart(dayofyear, DOB) END) 
      BETWEEN @TODAY and @TODAY + 9

Using the day of year means we can ignore leap years and year end - provided we check for "wrap" by adding the year length onto the day number.

This isn't perfect - in December of a leap year it may miss a day, or spot a day early - but for most circumstances that doesn't matter, as it's a "timely reminder" we are looking for. If you need precision, you just replace the constant 365 with the number of days in this year, which can be worked out by:

SQL

DATEPART(dayofyear, DATEADD(day, -1 , DATEFROMPARTS(DATEPART(year, DATEADD(year, 1, GETDATE())), 1, 1)))

But that's kinda messy.

SQL

DECLARE @TODAY INT = datepart(dayofyear, GETDATE())
DECLARE @DAYSINYEAR INT = DATEPART(dayofyear, DATEADD(day, -1 , DATEFROMPARTS(DATEPART(year, DATEADD(year, 1, GETDATE())), 1, 1)))
SELECT DOB
FROM Student
WHERE (CASE WHEN datepart(dayofyear, DOB) < @TODAY 
            THEN datepart(dayofyear, DOB) + @DAYSINYEAR
            ELSE datepart(dayofyear, DOB) END) 
      BETWEEN @TODAY and @TODAY + 9

History

2019-12-27 Original version

License

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