teradatainvalidationerror-code

Teradata Error 2665: Invalid Date

I am issuing the following SELECT on SQLA (Teradata version: 15.10.01.11):

select cast('2018-05-31' as date format 'yyyy-mm-dd') - interval '6' month;

And I am getting: SELECT Failed. 2665: Invalid Date. Any help would be much appreciated.

Br,

Shardul

Solution

  • According to Standard SQL subtracting 6 months from 2018-05-31 results in 2017-11-31 which obviously doesn't exist.

    Interval arithmetic can't be used for month/years (Feb 29th), better use ADD_MONTHS:

    ADD_MONTHS(DATE '2018-05-31', -6)

    There also oADD_MONTHS, which might return a different result for months end dates, e.g.

    ADD_MONTHS(DATE '2017-11-30', 6)  -> 2018-05-30
oADD_MONTHS(DATE '2017-11-30', 6) -> 2018-05-31