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

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

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

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

@ 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 ) ?"

yes this is regardless of the persons age

--Stalin

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

..........where dayofyear(date_birth) between dayofyear(st_date) and dayofyear(end_date)

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

it should be JULIAN_DAY(date)

what is JULIAN_DAY('2004-02-29')? what is JULIAN_DAY('2005-03-01')?

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)

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

If I'm not mistaken, the following should work:

recursive sql ... excellent idea !