Results 1 to 8 of 8

Thread: calculating age

  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: calculating age

    hi all,

    Iam trying different queries for calculating the age of a set of people whose data of birth is one of the fields in my table. Can someone help me out with it?

    Thks,
    Pradeep.

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

    Re: calculating age

    Have a look at the TIMESTAMPDIFF function in SQL Reference ...

    Link for db2manuals can be found in www.db2click.com

    If this function does not satisfy your needs, please post more details on what you would like to achieve


    Cheers
    Sathyaram

    Originally posted by pradeep_cis
    hi all,

    Iam trying different queries for calculating the age of a set of people whose data of birth is one of the fields in my table. Can someone help me out with it?

    Thks,
    Pradeep.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2003
    Posts
    7

    Re: calculating age

    A more direct answer please... if you can...(I want the query real quick... if it is simple)

    Thks,
    Pradeep.
    Originally posted by sathyaram_s
    Have a look at the TIMESTAMPDIFF function in SQL Reference ...

    Link for db2manuals can be found in www.db2click.com

    If this function does not satisfy your needs, please post more details on what you would like to achieve


    Cheers
    Sathyaram

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

    Re: calculating age

    SQL Reference extract :

    TIMESTAMPDIFF scalar function
    >>-TIMESTAMPDIFF--(--expression--,--expression--)--------------><



    The schema is SYSFUN.

    Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.

    The first argument can be either INTEGER or SMALLINT. Valid values of interval (the first argument) are:


    1
    Fractions of a second

    2
    Seconds

    4
    Minutes

    8
    Hours

    16
    Days

    32
    Weeks

    64
    Months

    128
    Quarters

    256
    Years
    The second argument is the result of subtracting two timestamps and converting the result to CHAR(22).

    The result of the function is INTEGER. The result can be null; if the argument is null, the result is the null value.

    The following assumptions may be used in estimating a difference:

    There are 365 days in a year.
    There are 30 days in a month.
    There are 24 hours in a day.
    There are 60 minutes in an hour.
    There are 60 seconds in a minute.
    These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

    Example:

    The following example returns 4277, the number of minutes between two timestamps:

    TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -
    TIMESTAMP('2001-09-26-12.07.58.065497')))
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2003
    Posts
    7

    Re: calculating age

    OK I am awful bad at this. I want to retrieve the current time and use it to compare with the birthday. Then calculate the age in years...

    Originally posted by sathyaram_s
    SQL Reference extract :

    TIMESTAMPDIFF scalar function
    >>-TIMESTAMPDIFF--(--expression--,--expression--)--------------><



    The schema is SYSFUN.

    Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.

    The first argument can be either INTEGER or SMALLINT. Valid values of interval (the first argument) are:


    1
    Fractions of a second

    2
    Seconds

    4
    Minutes

    8
    Hours

    16
    Days

    32
    Weeks

    64
    Months

    128
    Quarters

    256
    Years
    The second argument is the result of subtracting two timestamps and converting the result to CHAR(22).

    The result of the function is INTEGER. The result can be null; if the argument is null, the result is the null value.

    The following assumptions may be used in estimating a difference:

    There are 365 days in a year.
    There are 30 days in a month.
    There are 24 hours in a day.
    There are 60 minutes in an hour.
    There are 60 seconds in a minute.
    These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

    Example:

    The following example returns 4277, the number of minutes between two timestamps:

    TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -
    TIMESTAMP('2001-09-26-12.07.58.065497')))

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is an example of a query against the sample database:

    SELECT
    firstnme,
    midinit,
    lastname,
    birthdate,
    year(current date - birthdate) as age
    FROM employee
    WHERE year(current date - birthdate) >= 65;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select year(current_date) 
         - year(birthdate)
         - case when month(current_date)
                   > month(birthdate)
                then 0
                when month(current_date)
                   < month(birthdate)
                then 1
                when day(current_date)
                   < day(birthdate)
                then 1
                else 0   
             end             as age
      from yourtable
    rudy
    http://r937.com/

  8. #8
    Join Date
    Dec 2003
    Posts
    7
    Kool! Just what I wanted!
    Thankyou Marcus and Satyaram!

    Pradeep.

    Originally posted by Marcus_A
    Here is an example of a query against the sample database:

    SELECT
    firstnme,
    midinit,
    lastname,
    birthdate,
    year(current date - birthdate) as age
    FROM employee
    WHERE year(current date - birthdate) >= 65;

Posting Permissions

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