Age Calculation

Database
Enthusiast

Age Calculation

Hi,

I want to find difference between 2 dates and calculate age of the person in years

Example
1) DOB = 11/29/2001 Current Date = 1/1/2002 = 0 year
2) DOB = 11/29/2001 Current Date = 12/1/2002 = 1 year
3) DOB = 11/29/2001 Current Date = 4/1/2003 = 1 year
If count of months is >12 then = 1 year
If count of months > 12 but less than 24 then = 1 year

In other words only if the person has completed a full year should he be counted for that year.

Please help.

Sam
26 REPLIES
Enthusiast

Re: Age Calculation


SELECT DT1, DT2, DT2-DT1 YEAR
FROM MYTABLE;
Enthusiast

Re: Age Calculation

It works when i do this
select ( cast('2002-01-01' as date) - cast('2000-05-01' as date)) year as abc

However when i query the actual table it errors out saying "Interval field overflow"
Junior Contributor

Re: Age Calculation

Hi Sam,
intervals default to 2 digits, just modify that using year(4)

Dieter
Enthusiast

Re: Age Calculation

That worked

Thanks!
N/A

Re: Age Calculation

Hi,

I run the following :
select ( cast('2002-01-01' as date) - cast('2000-05-01' as date)) year(4) as abc;
select ( cast('2001-12-31' as date) - cast('2000-05-01' as date)) year(4) as abc;

First query gives me 2 and second query gives 1, this seems to be incorrect.
Fan

Re: Age Calculation

I am seeing the same issue, where only the years are being subtracted, with no regard to the number of months or days.

For example, I would expect this to return zero years, as it is only a 1-day difference:
cast(((cast('2010-01-01' as date)-cast('2009-12-31' as date)) year(4)) as integer) as tst_age

I am getting a value of 1, however. Does anyone know if this is how the function is designed, or is there some other issue?

Thanks.
Junior Contributor

Re: Age Calculation

Works as designed/according to Standard SQL.

You might use the "months_between" Oracle UDF or
((date1 (int)) - (date2 (int))) / 10000

Dieter
Enthusiast

Re: Age Calculation

While you subtract dates, the functions like day, month and year will return respective values in either days or months or years. In order to solve your problem, why do you nest the funtions;
Something like this:
SEL CAST(((CAST('2010-01-01' AS DATE)-CAST('2009-12-31' AS DATE)) MONTH(4)) AS INTEGER)/12 AS tst_age
I think it should work for you, since you always calculating age in years only.
Fan

Re: Age Calculation

Thanks for the responses.

I ended up using this calculation which gives desired results (calculates total days and divides by 365.25, which also accounts for leap years):

SELECT cast(((cast('2010-01-01' as date)-cast('2009-12-31' as date))/365.25 ) as integer) as tst_age