Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Substract 1 year from leap year - bug?
 
From:   Carr, Andy

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020