All Forums Database
Happy Gilmore 1 post Joined 04/08
30 Apr 2008
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

30 Apr 2008

hope this helpsselect year('2008-04-30') - YEAR('2000-01-01') as years , month('2008-04-30')-MONTH('2000-01-01') as monthsThanks

SN 77 posts Joined 01/07
30 Apr 2008

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

maclark 1 post Joined 12/11
15 Dec 2011

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.



dnoeth 4628 posts Joined 11/04
15 Dec 2011

Hi Mike,

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

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



hssahoo 2 posts Joined 12/11
19 Dec 2011

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;


rluebke 65 posts Joined 11/05
20 Dec 2011

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.


You must sign in to leave a comment.