# Thread: Date of birth Query help

1. Registered User
Join Date
Sep 2007
Posts
3

## Unanswered: Date of birth Query help

Need Help -- I am new to db2
I have a table emp(ename, date_birth)
now I want to select all the ename from emp table whose date_birth is in between st_date(input parameter) and end_date(input parameter)
example if st_date = '2006-12-25' and end_date = '2007-03-31'
I want all the records whose date_birth is falling between 12-25 and 03-31

Thanks & Regards,
Stalin

2. Registered User
Join Date
Dec 2005
Posts
273
This query might help:

SELECT ename, date_birth FROM emp
WHERE
( 1 = CASE WHEN MONTH(DATE(st_date))*100+DAY(DATE(st_date)) <=
MONTH(DATE(end_date))*100+DAY(DATE(end_date)) THEN 1 ELSE 0 END AND
MONTH(date_birth)*100+DAY(date_birth)
BETWEEN
MONTH(DATE(st_date))*100+DAY(DATE(st_date))
AND
MONTH(DATE(end_date))*100+DAY(DATE(end_date))
)
OR
( 1 = CASE WHEN MONTH(DATE(st_date))*100+DAY(DATE(st_date)) <
MONTH(DATE(end_date))*100+DAY(DATE(end_date)) THEN 0 ELSE 1 END AND
( MONTH(date_birth)*100+DAY(date_birth)
>= MONTH(DATE(st_date))*100+DAY(DATE(st_date))
OR
MONTH(date_birth)*100+DAY(date_birth)<=
MONTH(DATE(end_date))*100+DAY(DATE(end_date))
)
)

3. Registered User
Join Date
Sep 2007
Posts
3
Thanks a lot umayer.
This is a great logic.
But out of curiosity, are there any other ways we can do the same?

Regards,
Stalin

4. Registered User
Join Date
Jan 2003
Posts
4,310
I am not sure if I missed anything, but if date_birth, st_date, and end_date are all of type DATE, then this will do the trick:

select ename,date_birth from emp where date_birth between st_date and end_date

Andy

5. Registered User
Join Date
Dec 2005
Posts
273
@ ARWinner

I understood the original question as a:

"who of the EMPs celebrates his birthday between st_date and end_date ( regardless of the persons age ) ?"
Last edited by umayer; 09-20-07 at 09:41.

6. Registered User
Join Date
Sep 2007
Posts
3
yes this is regardless of the persons age

--Stalin

7. Registered User
Join Date
Dec 2005
Posts
273
this query should also work:

SELECT ename, date_birth FROM (
SELECT ename, date_birth ,
MAX (
CASE WHEN MONTH(date_birth)*100+DAY(date_birth)
BETWEEN MONTH(DATE(st_date))*100+DAY(DATE(st_date))
AND MONTH(DATE(end_date))*100+DAY(DATE(end_date))THEN 1 ELSE 0 END ,
CASE WHEN MONTH(date_birth)*100+DAY(date_birth)
BETWEEN MONTH(DATE(end_date))*100+DAY(DATE(end_date))
AND MONTH(DATE(st_date))*100+DAY(DATE(st_date)) THEN 1 ELSE 0 END )AS INOUT
FROM emp
) A
WHERE INOUT =
CASE WHEN MONTH(DATE(st_date))*100+DAY(DATE(st_date)) <
MONTH(DATE(end_date))*100+DAY(DATE(end_date)) THEN 1 ELSE 0 END
Last edited by umayer; 09-20-07 at 10:48.

8. Registered User
Join Date
Jul 2006
Location
Pune , India
Posts
433
..........where dayofyear(date_birth) between dayofyear(st_date) and dayofyear(end_date)

9. Registered User
Join Date
Jan 2003
Posts
4,310
Oops, sorry, I missed that.

This should also work:

select ename,date_birth from emp where (date_birth - (year(date_of_birth) - 1000) years) between (st_date - (year(st_date) - 1000 + (year(end_date) - year(st_date))) years) and (end_date - (year(end_date) - 1000) years)

Andy

10. Registered User
Join Date
Jul 2006
Location
Pune , India
Posts
433
it should be JULIAN_DAY(date)

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
what is JULIAN_DAY('2004-02-29')? what is JULIAN_DAY('2005-03-01')?

12. Registered User
Join Date
Dec 2005
Posts
273
rahul_s80:

simple solutions using DAYOFYEAR or JULIAN_DAY fail due to leap years.
Extra logic must be used to account them (and I didn't found that extra logic) .
Also consider that DAYOFYEAR(st_date) might be greater than DAYOFYEAR(end_date).
( e.g. st_date = '01.10.2007' and end_date = '01.01.2008' )
JULIAN_DAY() does not account, that the current age of the EMP shouldn't matter

ARWinner:

very nice query.
But it fails if st_date and end_date are not within the same year:
( e.g. st_date = '01.10.2007' and end_date = '01.01.2008' )
it should work if you extend your query:
select ename,date_birth
from emp
where (date_birth - (year(date_of_birth) - 1000 - year(st_date) + year(end_date) ) years)
between
(st_date - (year(st_date) - 1000 + (year(end_date) - year(st_date))) years)
and
(end_date - (year(end_date) - 1000) years)
Last edited by umayer; 09-21-07 at 03:16.

13. Registered User
Join Date
Jan 2003
Posts
4,310
Originally Posted by umayer
ARWinner:

very nice query.
But it fails if st_date and end_date are not within the same year:
( e.g. st_date = '01.10.2007' and end_date = '01.01.2008' )
it should work if you extend your query:
select ename,date_birth
from emp
where (date_birth - (year(date_of_birth) - 1000 - year(st_date) + year(end_date) ) years)
between
(st_date - (year(st_date) - 1000 + (year(end_date) - year(st_date))) years)
and
(end_date - (year(end_date) - 1000) years)
Actually both our queries are not quite right. For my query, using 12/08/2004 for date_of_birth, and your dates with my query results with comparing 12/08/1000 to 01/10/999 and 01/01/1000 which yields a false which is wrong. Using 01/08/2004 for DOB and 12/25/2007 for st_date and 03/01/2008 for end_date with your query nets comparing 01/08/999 to 12/25/999 and 03/31/1000 again yielding a false that should be true. I believe that the additional offset for DOB need to be conditional on whether the month of DOB is greater or equal to the month of st_date:

(date_birth - (year(date_of_birth) - 1000 - case when month(date_of_birth) >= month(st_date) then year(end_date) - year(st_date) else 0 end ) years)

Andy

14. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by stalin7k
I have a table emp(ename, date_birth)
now I want to select all the ename from emp table whose date_birth is in between st_date(input parameter) and end_date(input parameter)
If I'm not mistaken, the following should work:
Code:
```WITH birthdays(emp, d) AS
(SELECT emp, date_birth FROM emp
UNION ALL
SELECT emp, d + 1 YEAR FROM birthdays
WHERE d < end_date)
SELECT ename
FROM   birthdays
WHERE  d BETWEEN st_date and end_date```

15. Registered User
Join Date
Dec 2005
Posts
273
recursive sql ... excellent idea !

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•