Archives of the TeradataForum

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

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

<-- 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
Year to Month
Month
Day to Hour
Day to Minute
Hour
Hour to Minute
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>>