How to correctly find birthdays in the upcoming 30 days (TSQL)

hi experts
i'm looking for an T-SQL statement which find contacts, who have birthday within the next 30 days.
also take care of date-ranges going over the year-border (for example 20-12-2005 to 19-01-2006) and the 29-02 leap-year effekt.
i have tried quite some time already, but didn't succeed.
thank you for suggestions
cheers woolf
LVL 5
wlfsAsked:
Who is Participating?
 
BillAn1Commented:
jrb, clever idea, - determine thier age now, and their age in 30 days, and compare them!!
Unfortunately it won't quite work as is :-(
datediff does not give the number of whole years between 2 dates, it simpy gives the difference between the year components of the 2 dates,
so, e.g. datediff(year,'2000-05-30','2005-05-14') will give 5, even though someone born on May 30 2000 will not be 5 unitl May 30, and are only 4 today (may 14)

unfortunately, to calculate the age of someone is a bit more tricky in SQL - you need to compare not just the relevant years, but also when in the year the dates occur. You could try

select * from yourtable
where
datediff(Year, birthdate, getdate()) - case when dateadd(year,datediff(Year, birthdate, getdate()),birthdate) > getdate() then 1 else 0 end

datediff(Year, birthdate, dateadd(dd,30,getdate())) - case when dateadd(year,datediff(Year, birthdate, dateadd(dd,30,getdate())),birthdate) > dateadd(dd,30,getdate()) then 1 else 0 end

desipte the fact that this looks fairly cumbersome, it will be a lot more efficient than the function I suggested previously, since there is no need to loop
0
 
dbeneitCommented:
select * from sample where datebirday between
getdate() and DATEADD(month, 1, getdate() )  


getdate() (now with a sample)
0
 
BillAn1Commented:
Presumably your contacts table has their date of birth, not the date of their next birthday.
try a function to calculate the days as follows :

create function fnDays2NextBirthday(@birthdate datetime, @today datetime)
returns integer
as
begin
      declare @birthday datetime
      set @birthday = @birthdate
      while @birthday < @today set @birthday = dateadd(yy,1,@birthday)
      return datediff(dd,@today,@birthday)
end

then you can query your contacts by :

select * from contacts where dbo.fnDays2NextBirthday(birthdate,getdate()) <= 30
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dbeneitCommented:
one solution:

we calculate the age of "records" and add the years to original date. then we can compare
QUERY1
 select * from sample where
convert(varchar(10),dateadd(year,datediff(year,datebirthday,dateadd(day,-1,dateadd(month,1,getdate()))),datebirthday),120) between convert(varchar(10),getdate(),120) and
convert(varchar(10),dateadd(day,-1,dateadd(month,1,getdate())),120)


datebirthday -->your date field
getdate()-->for eixemple now
if you know the date ... for eixample '13/05/2005' then is simpler than QUERY1
QUERY2

 select * from sample where
convert(varchar(10),dateadd(year,datediff(year,datebirthday, '2005/06/12'),datebirthday),120)
between '2005/05/13'  and '2005/06/12'
0
 
RejojohnyCommented:
try this .. this should give u all birthdates coming up in the next 30 days starting from today ...

select birthtdate from urtable
where day(birthdate) beween day(getdate()) and day(dateadd(d, 30, getdate()))
  and month(birthdate) beween month(getdate()) and month(dateadd(d, 30, getdate()))

0
 
dbeneitCommented:
Rejojohny,

by birthdate 15-06-1972

test since18/05/2005 to 16/06/2005
15 between 18 and 17 --> false

dbeneit.
0
 
wlfsAuthor Commented:
to BillAn1:
you are right the birthdate lies in the past. I like the function approach and it seems to be a correct solution.
just one question, where exactly would i create the function, within EM (userdefined function ??).

to dbeneit:
although i would prefer one SQL statement, it looks to be quite challenging.
in celkos sql for smarties (SQL92 product independent) the approuch is
SELECT name, dob, CURRENT_DATE
FROM Employees
WHERE INTERVAL (CURRENT_DATE - birthday) YEAR < INTERVAL (CURRENT_DATE - birthday + INTERVAL 45 DAYS) YEAR;
the problem is afaik there is no implementation in MS T-SQL for INTERVAL, which means years of age. DATEDIFF just calculates the difference between the year-numbers of two dates, right?

for your statement although i haven't tested it, i would think you will have a problem with a birthday lets say 02/01/1970 and an observation range starting on 30/12/2005. You convert the birthday to 02/01/2005, right? and therefore it wouldn't show up

to Rejojohny
i think there is a problem.
0
 
dbeneitCommented:
wlfs,


DATEDIFF just calculates the difference between the year-numbers of two dates, right? --> yes. the completed years

for your statement although i haven't tested it, i would think you will have a problem with a birthday lets say 02/01/1970 and an observation range starting on 30/12/2005. You convert the birthday to 02/01/2005, right? and therefore it wouldn't show up
-->
It isn't right. The years diference is with end date interval. Then i convet the birthday to 02/01/2006 that it's between 30/12/2005 and 29/01/2006
0
 
BillAn1Commented:
In EM you can create the function as you say in "User Defined Functions". Right-click and select "new user defined function". IN the box that pops up, replace the sample text with the complete text given above, i.e.

create function fnDays2NextBirthday(@birthdate datetime, @today datetime)
returns integer
as
begin
     declare @birthday datetime
     set @birthday = @birthdate
     while @birthday < @today set @birthday = dateadd(yy,1,@birthday)
     return datediff(dd,@today,@birthday)
end
0
 
jrb1Commented:
Here it is in a simple SQL statement:

select contactinfo
from yourtable
where datediff(Year, birthdate, getdate()) < datediff(Year, birthdate, DateAdd(Day, 30, getdate()));
0
 
jrb1Commented:
I forgot that feature of date diff.

This looks like a variation of yours...I took a function to actually calcluate the age:

select CASE
WHEN dateadd(year, datediff (year, d1, d2), d1) > d2
THEN datediff (year, d1, d2) - 1
ELSE datediff (year, d1, d2)
END as Age

And stuck it into the query.  

select *
from yourtable
where CASE WHEN dateadd(year, datediff (year, birthdate, getdate()), birthdate) > getdate()
                    THEN datediff (year, birthdate, getdate()) - 1
                    ELSE datediff (year, birthdate, getdate()) END <
          CASE WHEN dateadd(year, datediff (year, birthdate, dateadd(day,30,getdate())), birthdate) > 
                                                                                                       dateadd(day,30)getdate())
                    THEN datediff (year, birthdate, dateadd(day,30,getdate())) - 1
                    ELSE datediff (year, birthdate, dateadd(day,30,getdate())) END
0
 
BillAn1Commented:
yep, same only different :-)
0
 
wlfsAuthor Commented:
to dbeneit: your example with other date range and imagine datebirthday = 1970/12/20
select * from sample where
convert(varchar(10),dateadd(year,datediff(year,datebirthday, '2006/01/12'),datebirthday),120)
between '2005/12/13'  and '2006/01/12'

convert(varchar(10), dateadd(year, 36, datebirthday),120) between ...
convert(varchar(10), 2006/12/20, 120) between '2005/12/13' and '2006/01/12'   FALSE !!!
nevertheless i liked the idea of converting to varchar.

to jrb1+BillAn1
i think the idea of comparing the age was first mentioned in the Celko approach.
BillAn1 helped me most, although I eventually used the SQL-wording from jrb1.
therefore the points.

to all:
thanks a lot for your valuable input.
it seems MS-TSQL misses the INTEVAL function, which would make things much easier.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.