# Thread: date difference - calculating age

1. Registered User
Join Date
May 2002
Posts
3

## 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. Super Moderator
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. Registered User
Join Date
May 2002
Posts
3

## Re: Re: date difference - calculating age

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

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

Cheers
Ari

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

5. Super Moderator
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. Registered User
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
•