Tenure in years and months between two dates

Database

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
SN
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)'

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

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