Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    3

    Question Unanswered: date difference - calculating age

    In Orcale there is a function MONTHS_BETWEEN and in SQL Server DateDiff which help to calculate age from a birthdate. Is there a similar function in DB2 to get a difference of dates (in months)?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: date difference - calculating age

    I can't remember any, but you can do this using other functions.

    For eg,

    C:\>db2 with t1(days) as (values(date('2000-10-05') - date('1899-12-08'))) select year(days)*12+month(days) from t1


    or create an UDF ...

    HTH

    Cheers

    Sathyaram

    Originally posted by Ari
    In Orcale there is a function MONTHS_BETWEEN and in SQL Server DateDiff which help to calculate age from a birthdate. Is there a similar function in DB2 to get a difference of dates (in months)?
    Last edited by sathyaram_s; 05-30-02 at 09:36.

  3. #3
    Join Date
    May 2002
    Posts
    3

    Smile Re: Re: date difference - calculating age

    Thanks, Sathyaram. Your answer led me to a working solution:
    select current date, date_1 , integer( floor( (current date - date_1 ) / 10000 )) from table_1. This works now: difference between
    1990-06-19 and 2002-06-19 => 12 years, and the difference 1990-06-20 and 2002-06-19 => 11 years.

    Why i have to divide with 10000 is still unclear...
    Your answer was the best until now!

    Cheers
    Ari


    Originally posted by sathyaram_s
    I can't remember any, but you can do this using other functions.

    For eg,

    C:\>db2 with t1(days) as (values(date('2000-10-05') - date('1899-12-08'))) select year(days)*12+month(days) from t1


    or create an UDF ...

    HTH

    Cheers

    Sathyaram


  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Re: Re: date difference - calculating age

    The date difference ( current date - date_1) is a DECIMAL(8,0) of the form yyyymmdd.

    This explains '/10000' - chop the last four (mmdd) digits off the result.


    Cheers

    Sathyaram



    Originally posted by Ari
    Thanks, Sathyaram. Your answer led me to a working solution:
    select current date, date_1 , integer( floor( (current date - date_1 ) / 10000 )) from table_1. This works now: difference between
    1990-06-19 and 2002-06-19 => 12 years, and the difference 1990-06-20 and 2002-06-19 => 11 years.

    Why i have to divide with 10000 is still unclear...
    Your answer was the best until now!

    Cheers
    Ari


    Last edited by sathyaram_s; 06-19-02 at 11:13.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Re: Re: Re: date difference - calculating age

    Missed this one :

    Looks like you are interested only in the year portion of the result ... see if selecting year( current date - date_1) helps .

    Cheers

    Sathyaram


    Originally posted by sathyaram_s
    The date difference ( current date - date_1) is a DECIMAL(8,0) of the form yyyymmdd.

    This explains '/10000' - chop the last four (mmdd) digits off the result.


    Cheers

    Sathyaram




  6. #6
    Join Date
    May 2002
    Posts
    3
    Sathyaram


    Yess! It works. This shows how powerful SQL is - and how clever you are! The simplest solution is the best.

    With respect
    Ari

Posting Permissions

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