Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

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

Posted on 2005-05-10
Medium Priority
4,392 Views
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
0
Question by:wlfs
• 4
• 4
• 2
• +2

LVL 9

Expert Comment

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

getdate() (now with a sample)
0

LVL 17

Expert Comment

ID: 13973415
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

LVL 9

Assisted Solution

dbeneit earned 200 total points
ID: 13974414
one solution:

we calculate the age of "records" and add the years to original date. then we can compare
QUERY1
select * from sample where

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

select * from sample where
between '2005/05/13'  and '2005/06/12'
0

LVL 26

Expert Comment

ID: 13976234
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

LVL 9

Expert Comment

ID: 13976846
Rejojohny,

by birthdate 15-06-1972

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

dbeneit.
0

LVL 5

Author Comment

ID: 13979449
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

LVL 9

Expert Comment

ID: 13980043
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

LVL 17

Expert Comment

ID: 13980851
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

LVL 25

Expert Comment

ID: 14002962
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

LVL 17

Accepted Solution

BillAn1 earned 1400 total points
ID: 14003179
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

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

LVL 25

Assisted Solution

jrb1 earned 400 total points
ID: 14003210
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 <
THEN datediff (year, birthdate, dateadd(day,30,getdate())) - 1
ELSE datediff (year, birthdate, dateadd(day,30,getdate())) END
0

LVL 17

Expert Comment

ID: 14003539
yep, same only different :-)
0

LVL 5

Author Comment

ID: 14025601
to dbeneit: your example with other date range and imagine datebirthday = 1970/12/20
select * from sample where
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦