15,608,772 members
Articles / Programming Languages / SQL
Tip/Trick
Posted 26 Dec 2019

2.8K views

# Select users with birthdate in the next 10 days

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

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?

 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
 Re: Another version OriginalGriff27-Dec-19 20:06 OriginalGriff 27-Dec-19 20:06
 Again: "Try that on Dec 27th, and a birthday on Jan 1st..." You have to test these things! If I run all three queries together: SQL ```SELECT * FROM Student WHERE CAST(MONTH(DOB) AS VARCHAR(32)) + '/' + CAST(DAY(DOB) AS VARCHAR(32)) + '/' + CAST(YEAR(GetDAte()) AS VARCHAR(32)) BETWEEN GetDate() AND DATEADD (D, 10, GetDate()) 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))) 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``` The first two give no rows, the third gives one. "I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
 Last Visit: 31-Dec-99 18:00     Last Update: 22-Mar-23 22:05 Refresh 1