Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 23 Feb 2010 @ 12:22:02 GMT

  <Prev Next>   <<First <Prev

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:

        ,(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.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023