All Forums General
eche 3 posts Joined 05/14
12 May 2014
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.

 

dnoeth 4628 posts Joined 11/04
12 May 2014

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

 

Dieter

eche 3 posts Joined 05/14
12 May 2014

Thanks Dieter! your answer worked out neat.

You must sign in to leave a comment.