Error 2666 Invalid Timestamp

Database
Highlighted
Fan

Error 2666 Invalid Timestamp

Any idea why this has started failing after running successfully for some weeks?

Cast(SOP.Original_ORDERED_TS as date format 'YY/MM/DD' ) >= Current_Date - (interval '25' month)

I have had problems with leap year and interval day but thought that month interval was supposed to work. My first failure was on 3/29 so I don't know if it is related or not.
3 REPLIES
Enthusiast

Re: Error 2666 Invalid Timestamp

At a wild guess, maybe daylight savings is a factor. There have been changes to daylight savings. One of your date columns might be using daylight savings whilst the other is not, hence one date is 'behind' the other and causing an error.

Some PC's are patched and have moved to daylight savings already.

Cheers

Tim
Teradata Employee

Re: Error 2666 Invalid Timestamp

Because DATE - INTERVAL MONTH leaves the "day of month" unchanged.
So date'2008-03-31' - INTERVAL '25' MONTH would be 2006-02-31

Use ADD_MONTHS(CURRENT_DATE ,-25) instead. If the day of month is too large, it will be adjusted to last valid day in the month. For example, ADD_MONTHS(date'2008-03-31',-25) = date'2006-02-28'
Fan

Re: Error 2666 Invalid Timestamp

Thanks for the solution. I thought it was something like that but didn't know what to change the condition to.