15,667,738 members
Articles / Programming Languages / SQL
Tip/Trick
Posted 26 Dec 2019

2.9K views

# Select users with birthdate in the next 10 days

Rate me:
0.00/5 (No votes)
26 Dec 2019CPOL
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)

Written By
CEO
Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

## Comments and Discussions

 First Prev Next
 Another Version Displaying the Birthday date JD-Gabriiel30-Dec-19 22:01 JD-Gabriiel 30-Dec-19 22:01
 Another version Michael Ecklin27-Dec-19 4:57 Michael Ecklin 27-Dec-19 4:57
 Re: Another version OriginalGriff27-Dec-19 8:53 OriginalGriff 27-Dec-19 8:53
 Re: Another version Michael Ecklin27-Dec-19 9:24 Michael Ecklin 27-Dec-19 9:24
 SELECT * FROM Student WHERE DOB BETWEEN CAST(MONTH(DOB) AS VARCHAR(32)) + '/' + CAST(DAY(DOB) AS VARCHAR(32)) + '/' + CAST(YEAR(GetDAte()) AS VARCHAR(32)) AND DATEADD (D, 10, CAST(MONTH(DOB) AS VARCHAR(32)) + '/' + CAST(DAY(DOB) AS VARCHAR(32)) + '/' + CAST(YEAR(GetDAte()) AS VARCHAR(32)))
 Re: Another version OriginalGriff27-Dec-19 20:06 OriginalGriff 27-Dec-19 20:06
 Last Visit: 31-Dec-99 18:00     Last Update: 4-Jun-23 7:07 Refresh 1

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.