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)
|