Archives of the TeradataForum

Message Posted: Wed, 26 May 2004 @ 13:39:03 GMT

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

Hi Rudel,

I discovered this problem recently too. It's not just a leap year issue. If you use the interval method of date calulations Teradata seems to calculate to exactly the same day of the month. Examples:

Your one:

     SELECT CAST('2004-02-29' AS DATE)  - INTERVAL '12' MONTH

Others that give same problem:

     /* because there is no 31st of June */
     SELECT CAST('2004-07-31' AS DATE)  - INTERVAL '1' MONTH

     /* because there is no 30th of Feb */
     SELECT CAST('2004-04-30' AS DATE)  - INTERVAL '2' MONTH

     /* because there is no 30th of Feb */
     SELECT CAST('2004-04-30' AS DATE)  + INTERVAL '10' MONTH

If the month that the calculation lands on doesn't contain the day of the start date then you get the invalid date message.

Sorry if the explanation is bad but hopefully the examples make sense. As the others have suggested use the add_months function. It deals with leap years and the problems identified above.

Cheers,

Andy

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