Database
Not applicable

## Tenure in years and months between two dates

Hi,

Can someone tell me how to extract the tenure between two dates as years and months using SQL?

thanks HG
6 REPLIES

## Re: Tenure in years and months between two dates

hope this helps

select year('2008-04-30') - YEAR('2000-01-01') as years , month('2008-04-30')-MONTH('2000-01-01') as months

Thanks
Enthusiast

## Re: Tenure in years and months between two dates

you can try this,

sel
Cast((((('2009-02-01')(date))-(('2008-01-01')(date)))year(4)) As integer)||'year(s)'||
Cast((((('2009-02-01')(date))-(('2008-01-01')(date)))month(4)) As integer) mod 12||'month(s)'
Not applicable

## Re: Tenure in years and months between two dates

This doesn't seem to work correctly.

select Cast((((('2007-01-31')(date))-(('2005-07-01')(date))) year(4)) As integer) as years;

This gives the result of 2 which is incorrect.  There is actually only 1 year and 6 months between the two dates.

Anybody have a solution to determine the correct number of FULL years between two dates?  I know I can get the number of days and divide by 365 but that doesn't work when leap years are invovled.

Thanks,

Mike

Junior Contributor

## Re: Tenure in years and months between two dates

Hi Mike,

2 is only "incorrect" for a human being, but "correct" for Standard SQL :-)

`SELECT   DATE '2007-01-31' AS dt1,   DATE '2005-07-01' AS dt2,  ((dt1 (INT)) - (dt2 (INT))) / 10000 AS age`

Dieter

Fan

## Re: Tenure in years and months between two dates

SELECT  date '2011-02-28' as dt1,

date '2000-07-29' as dt2,

(dt1 - dt2) as no_of_days,

(dt1 - dt2)/365 as no_of_yrs1,

cast((dt1 - dt2)/365.25 as integer) as no_of_yrs2,

extract (year from dt1) - extract (year from dt2)

- case  when extract(month from dt1) lt extract(month from dt2) then 1

else (case when extract(day from dt1) lt extract(day from dt2) then 1 else 0 end) end as no_of_yrs3;

Enthusiast

## Re: Tenure in years and months between two dates

Can I suggest you use the SYS_CALENDAR.CALENDAR for doing this?  Even better, queries using it are easy to understand and maintain. It is, however, very Teradata-ish.

R