Results 1 to 15 of 15
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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. #5
    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. #6
    Join Date
    Sep 2007
    Posts
    3
    yes this is regardless of the persons age

    --Stalin

  7. #7
    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. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    ..........where dayofyear(date_birth) between dayofyear(st_date) and dayofyear(end_date)
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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. #10
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    it should be JULIAN_DAY(date)
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is JULIAN_DAY('2004-02-29')? what is JULIAN_DAY('2005-03-01')?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    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. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote 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. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    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
  •