Getting error 2666. Invalid dates supplied for CALDATES.cdate

General
Fan

Getting error 2666. Invalid dates supplied for CALDATES.cdate

Hi,

I'm new to teradta and when I'm running the following SQL:

SELECT 

cast(CALENDAR_DATE as date format 'YYYY-MM-DD')    -  INTERVAL '1' YEAR

FROM SYS_CALENDAR.CALENDAR

and get error 2666. Invalid dates supplied for CALDATE.

I don't see anything wrong in the format of caldate, in fact sys_calendar.calendar doesn't even have an attribute called CALDATE.

can anyone help me understand this?

Thanks in advance.

2 REPLIES
Junior Contributor

Re: Getting error 2666. Invalid dates supplied for CALDATES.cdate

SysCalendar.Calendar is a view, the column CALDATE comes from the base table.

Your problem is the (IHMO stupid) ANSI Interval calculation, e.g. DATE '2012-02-29' - INTERVAL '1' YEAR results in 2011-02-29, which simply doesn't exist.

Nobody ever uses Intervals for months or years as there's good ol' ADD_MONTHS :-)

SELECT
CALENDAR_DATE,
ADD_MONTHS(CALENDAR_DATE, -12)
FROM SYS_CALENDAR.CALENDAR
Fan

Re: Getting error 2666. Invalid dates supplied for CALDATES.cdate

Thanks Dieter! your answer worked out neat.