Age calculation in DB2

[ari1]

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)?

 

[PruSQLer]

I don't know of a similar function in DB2. I use this code when I want to get differences in date:

Select ((year(date_1) - year(date_2)) * 12) +
month(date_1) - month(date_2)

You could try posting this question in the DB2 forum to see if anyone there knows of a function I haven't heard of.

 

[ari1]

Thank you, but I can't see how this works? Multiply years by 12? The problem is, that difference between
1990-06-19 and 2002-06-19 => 12 years, and the differnece 1990-06-20 and 2002-06-19 => 11 years.

 

[ari1]

Ifound a working solution with help from Sathyaram (dBforums):
select current date, date_1 , integer( floor( (current date - date_1 ) / 10000 )) as age 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...

 

[angiole]

Just a thought, you may be able to simply substract the higher date from the lower one.
Your result should be the number of days between.

 

[ari1]

Dear angiole
In DB2 the difference comes in format yymmdd, that's why it must first be divided by 10000 to get eg 12,0512 and then "floored" to 12,0 and finally "integered" to 12, meaning the age of 12.
Ari

 

[justsql]

Hi-

You can use the function timestaffdiff.

 

[justsql]

Hi-

You can use the function timestampdiff.