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

select * from sample where datebirday between
getdate() and DATEADD(month, 1, getdate() )  


getdate() (now with a sample)

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

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

Rejojohny,

by birthdate 15-06-1972

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

dbeneit.

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.

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

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

Here it is in a simple SQL statement:

select contactinfo
from yourtable
where datediff(Year, birthdate, getdate()) < datediff(Year, birthdate, DateAdd(Day, 30, getdate()));

yep, same only different :-)

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.