Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 01 Nov 2001 @ 19:32:27 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Teradata and ANSI Date INTERVAL
From:   Geoffrey Rommel

  Does anyone know why the INTERVAL function used in the SQL statement below does not work (I know that I can use ADD_MONTHS to achieve this but it seems unreasonable that this would generate an error):  

  select ('10/31/2001' (date, format 'MM/DD/YYYY')) - INTERVAL '13' MONTH  

  ...It would seem by the documentation that this bug was included as a "FEATURE" of ANSI SQL 99. It occurs when you are on the 31st day of a month and are going back or forward to a date with only 30 days. What kind of mathematical error would cause this???  

Dara, I have not read (or even seen) the ANSI SQL definition, but this makes sense to me. In the magical world of ANSI standards, intervals are defined in, shall we say, the "naive" sense often favored by business users. In this case, an interval of "exactly one month" means that the endpoints must fall on the same day of the month. (Credit card billing cycles would be one obvious application of this rule.) Thus the interval from Feb. 5 to Mar. 5 (28 days) is 1 month, and the interval from July 5 to Aug. 5 (31 days) is also 1 month. Since 9/2000 had no 31st day, your expression is (ahem) poorly formed. This is not a mathematical error or a bug, but a consequence of the definition of an "interval".

Personally, I hate intervals and avoid them whenever possible, but there they are. I believe all this goes back many years to a proposal originally made by C. J. Date.

Obviously, there are situations where the behavior described is not what you want, which is precisely why Teradata provides the ADD_MONTHS function.


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