Getting error 2666. Invalid dates supplied for CALDATES.cdate

General
Highlighted
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 2
Ambassador

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.