 |
 |
Archives of the TeradataForum
Message Posted: Thu, 01 Nov 2001 @ 19:32:27 GMT
| 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.
--wgr
| |
