Birthdays

Database

Birthdays

I an trying to create a query that will select all customers whos birthday is in the next seven days. I thought that this would be relatively straight forward but I having problems. I am new to Teradata data so by ignorance my stem from this fact
6 REPLIES
Enthusiast

Re: Birthdays

This should work...

sel emp_name,emp_dob
from emp
where emp_dob between current_date and current_date + 7
;

Re: Birthdays

Thanks for post, your suggestion was also my approach as well, but the following code returns no rows

sel BIRTH_DT
from UNICA_PROD_SANDBOX.rg_asda_dob
where BIRTH_DT between current_date and current_date + 7

Re: Birthdays

Hi,
your problem is that you included the birth-year.
Try something like this:

select ((cast(birthdate as int) mod 10000) - (cast(current_date as int) mod 10000)) as diff
from ....
where diff between 0 and 7;

Re: Birthdays

Andom

Thanks for post, it worked a treat.

Re: Birthdays

please consider also the case when the query is run at the end of a year. it is just a concept. maybe you'll need to tinker around a little.
Junior Contributor

Re: Birthdays

The best algorithm for that checks if the age changed between CURRENT_DATE and CURRENT_DATE + x:

WHERE ((CURRENT_DATE (INT)) - (birthdate (INT))) / 10000
<> ((CURRENT_DATE + 7 (INT)) - (birthdate (INT))) / 10000

Of course that age calculation is not very intuitive, so you might check if there's a UDF for it in your system.

Dieter