Archives of the TeradataForum

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

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

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>		<<First	<Prev	Next>	Last>>