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
13
Medium Priority
?
4,392 Views
Last Modified: 2008-01-09
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
Comment
Question by:wlfs
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 9

Expert Comment

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


getdate() (now with a sample)
0
 
LVL 17

Expert Comment

by:BillAn1
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

by:dbeneit
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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:Rejojohny
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

by:dbeneit
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

by:wlfs
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

by:dbeneit
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

by:BillAn1
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

by:jrb1
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

by:
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

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
 
LVL 25

Assisted Solution

by:jrb1
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 <
          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
 
LVL 17

Expert Comment

by:BillAn1
ID: 14003539
yep, same only different :-)
0
 
LVL 5

Author Comment

by:wlfs
ID: 14025601
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question