Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 03 May 2006 @ 12:32:34 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Subtracting month from a date
From:   The TeradataForum

We discovered a problem that I thought others might be interested in.

The problem can occur when using INTERVAL to subtract a month from a date where the previous month has fewer days than the current month. If the current date is 2006-05-31 and then using INTERVAL to subtract 1 month from the current date results in an illegal date: 2006-04-31 (April has only 30 days).

For example:

     SELECT (1060530 (DATE)) (title 'this month'),
               ((1060530 (DATE)) - INTERVAL '1' MONTH) (title 'last month')

         this month      last month
         2006-05-30      2006-04-30

     SELECT (1060531 (DATE)) (title 'this month'),
               ((1060531 (DATE)) - INTERVAL '1' MONTH) (title 'last month')

             2665:  Invalid date.

     SELECT (1060601 (DATE)) (title 'this month'),
               ((1060601 (DATE)) - INTERVAL '1' MONTH) (title 'last month')

         this month      last month
         2006-06-01      2006-05-01

The way to address this problem is by using the ADD_MONTHS function:

     sel(add_months (date, -1));

With ADD_MONTHS, the last day for the previous month is given for those cases where the date doesn't exist. So instead of 2006-04-31, ADD_MONTHS will return 2006-04-30.

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