 |
 |
Archives of the TeradataForum
Message Posted: Tue, 23 Feb 2010 @ 12:22:02 GMT
| Subj: | | Re: Age calculation |
| |
| From: | | Dieter Noeth |
Anomy.Anom wrote:
| | Per V2R6.2 DR96278 (see Tech Alert NTA1553): the TD12.0 behavior is correct. | |
| | According to ANSI SQL standard, units smaller than the unit of the result are to be ignored in the calculation. So, when computing
difference in years for two date values, the month and day values in each of the two operands should be ignored. | |
The interesting part is how other vendors claiming Standard SQL compliance implemented it, e.g. Mimer (www.mimer.com) returns the pre-V2R6.2
result.
I tried to read the SQL Standard documents, but you need a twisted mind to understand it and i'm not a lawyer :-)
This works:
sel
cdate
,(date-cdate) year(4) as age_ANSI
,((DATE (INT)) - (cdate (INT))) / 10000 as age_common_sense from
Sys_Calendar.caldates where
cdate between '2009-01-01' and '2009-12-31'
order by 1
There's the Oracle MONTHS_BETWEEN function, too.
Dieter
| |
