Click here to Skip to main content
15,562,410 members
Articles / Programming Languages / SQL
Tip/Trick
Posted 26 Dec 2019

Tagged as

Stats

2.7K views

Select users with birthdate in the next 10 days

Rate me:
Please Sign up or sign in to vote.
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 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

 
QuestionAnother Version Displaying the Birthday date Pin
JD-Gabriiel30-Dec-19 23:01
JD-Gabriiel30-Dec-19 23:01 
create table dbo.users(
	id int Identity(1,1),
	firstName varchar(100),
	lastName varchar(100),
	dateOfBirth date	
	)

insert into dbo.users
values('Dean','Gabriel','1986-07-02')
insert into dbo.users
values('Jeffrey','Kennedy','1988-08-31')
insert into dbo.users
values('Jameel','Parker','1992-05-22')
insert into dbo.users
values('David','Swartz','1986-09-25')
insert into dbo.users
values('Marc','Laubser','1986-08-06')
insert into dbo.users
values('Wesley','Payne','2000-02-29')

declare 
	@today date = getdate(),
	@days int = 200

declare 
	@currentMonth int = datepart(MONTH,@today),
	@currentDay int = datepart(day,@today),
	@currentYear int =  datepart(year,@today),
	@currentYearPlusDays int = datepart(year,dateadd(day,@days,@today)),
	@bitleapYear bit

set @bitleapYear = @currentYearPlusDays % 4

select 
	*,
	case @bitleapYear
	when 0 then convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-',DATEPART(DAY,dateofBirth))),121) 
	when 1 then 
		case 
			when convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-01'))) = concat(@currentYearPlusDays,'-02-01') then dateadd(year,4,concat(@currentYear,'-',datepart(month,dateOfBirth),'-',datepart(day,dateofBirth)))
			else convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-',DATEPART(DAY,dateofBirth))),121) 
			end 
	end as Birthday

from dbo.users
where 
	
	case @bitleapYear
	when 0 then convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-',DATEPART(DAY,dateofBirth))),121) 
	when 1 then 
		case 
			when convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-01'))) = concat(@currentYearPlusDays,'-02-01') then dateadd(year,4,concat(@currentYear,'-',datepart(month,dateOfBirth),'-',datepart(day,dateofBirth)))
			else convert(date,(concat(@currentYearPlusDays,'-',DATEPART(MONTH,dateOfBirth),'-',DATEPART(DAY,dateofBirth))),121) 
			end 
	end
	between @today and DATEADD(day,@days,@today)

QuestionAnother version Pin
Michael Ecklin27-Dec-19 5:57
Michael Ecklin27-Dec-19 5:57 
AnswerRe: Another version Pin
OriginalGriff27-Dec-19 9:53
mvaOriginalGriff27-Dec-19 9:53 
GeneralRe: Another version Pin
Michael Ecklin27-Dec-19 10:24
Michael Ecklin27-Dec-19 10:24 
GeneralRe: Another version Pin
OriginalGriff27-Dec-19 21:06
mvaOriginalGriff27-Dec-19 21:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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