Archives of the TeradataForum
Message Posted: Wed, 26 May 2004 @ 13:39:03 GMT
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:
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|