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.
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
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;
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.