1. Registered User
Join Date
Dec 2003
Posts
7

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,

2. Super Moderator
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

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,

3. Registered User
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,
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. Super Moderator
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')))

5. Registered User
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. Registered User
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;

7. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
Join Date
Dec 2003
Posts
7
Kool! Just what I wanted!
Thankyou Marcus and Satyaram!

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
•