Teradata Error 2665:无效日期问题求助（版本15.10.01.11）

The root cause of your error is simple: when you subtract 6 months from 2018-05-31, you’re attempting to calculate 2017-11-31—but November only has 30 days. Teradata doesn’t automatically roll over to the next valid date (like December 1st) in this scenario, hence the 2665 Invalid Date message.

Solution 1: Use the ADD_MONTHS Function #

Teradata’s ADD_MONTHS function is specifically designed to handle these edge cases. It adjusts the date to the last valid day of the target month if the original day doesn’t exist. Here’s your corrected query:

SELECT ADD_MONTHS(CAST('2018-05-31' AS DATE FORMAT 'yyyy-mm-dd'), -6);

This will return 2017-11-30, a valid date.

Solution 2: Manual Adjustment (If You Prefer Interval Arithmetic) #

If you want to stick with interval subtraction, you can first adjust the date to the first day of the current month, subtract the interval, then fetch the last day of the resulting month:

SELECT LAST_DAY(
    CAST('2018-05-31' AS DATE FORMAT 'yyyy-mm-dd') 
    - INTERVAL '6' MONTH 
    + INTERVAL '0' DAY -- Ensures we start from the first day of the original month
);

This also results in 2017-11-30, avoiding the invalid date error.

Either approach will resolve your issue by ensuring you end up with a valid calendar date.

内容的提问来源于stack exchange，提问作者Shardul