Sunday, 15 December 2019

SQL query to get the persons whose date of birthday falls in next specified no of days

-- SQL Query to pull the persons whose date of birth falls in the next specified no of days.

Here is the sample query for the same.

create table #empdata (eid int identity(1,1), dob date)
go
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-31'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-01-01'
insert into #empdata (dob) select '2000-01-02'
insert into #empdata (dob) select '2000-12-03'
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-26'
go
declare @today date = '2019-12-28'
declare @noofdays int = 10
declare @enddate date = dateadd(dd,@noofdays,@today)
select * from (
select *
, datediff(dd 
           ,@today 
       , dateadd(yy
,iif(year(@today) <> year(@enddate) and month(dob)=1
         ,datediff(yy,dob,@today)+1
     ,datediff(yy,dob,@today))
             ,dob)
  ) as [noOfDaysToGo]
from #empdata
) as persons where noOfDaysToGo BETWEEN 0 AND @noofdays
go


Results:
---------
eid dob noOfDaysToGo
1 2000-12-30 2
2 2000-12-31 3
3 2000-12-29 1
4 2000-12-29 1
5 2000-01-01 4
6 2000-01-02 5
8 2000-12-30 2


No comments:

Post a Comment