Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 04 Jun 2008 @ 16:09:20 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Change in casting to Interval
From:   Anomy Anom

<-- Anonymously Posted: Wed, 4 Jun 2008 10:02 -->

Excerpts from Tech Alert# NTA 1553

According to ANSI SQL standard, units smaller than the unit of the result are to be ignored in the calculation. So, [for example,] when computing difference in months for two date values, the day values in each of the two operands should be ignored.

There is a change in the behavior of the following interval types from V2R6.2 due to fix of DR 96278.

Year to Month
Day to Hour
Day to Minute
Hour to Minute

From a computing/mathematical view, there is an issue of 'betweenness' one expects that if the dates A, B, and C are in chronological order, then C-A = C-B + B-A. The SQL standard definition preserves that property, but the implementation before V2R6.2 does not.

The above can pose problems for customers that were relying on the prior behavior. While there are a number of ways around this issue using SQL, the cleanest solution is a UDF.

For example,

     Sel (DATE '2007-05-10' - DATE '2007-04-28')Month;

Returns 0 prior to DR96278 (because the difference is not a "full month"), but returns 1 after the fix is applied.

The Tech Alert in T@YS also has sample UDF code attached.

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