Interval subtraction throws exception when calculating date in February
Article ID: KB0083004
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | - |
Description
Error evaluating expression.
at AnonymousProcedure (line 5) caused by: Date '2015-2-29' not valid for month specified from operation DATE '2016-04-29' - INTERVAL '14' MONTH. [func-3954000][script-1900009]
Date '2015-2-29' not valid for month specified from operation DATE '2016-04-29' - INTERVAL '14' month. [FUNC-3954000]
PROCEDURE:
PROCEDURE testDate()
BEGIN
DECLARE badDt DATE DEFAULT CAST('2014/04/29' AS DATE);
SET badDt = CURRENT_DATE - INTERVAL '14' MONTH;
CALL PRINT ('I calculated a date of ' || badDt);
END”
at AnonymousProcedure (line 5) caused by: Date '2015-2-29' not valid for month specified from operation DATE '2016-04-29' - INTERVAL '14' MONTH. [func-3954000][script-1900009]
Date '2015-2-29' not valid for month specified from operation DATE '2016-04-29' - INTERVAL '14' month. [FUNC-3954000]
PROCEDURE:
PROCEDURE testDate()
BEGIN
DECLARE badDt DATE DEFAULT CAST('2014/04/29' AS DATE);
SET badDt = CURRENT_DATE - INTERVAL '14' MONTH;
CALL PRINT ('I calculated a date of ' || badDt);
END”
Issue/Introduction
Interval subtraction throws exception when calculating date in February
Resolution
This is the standard ANSI behavior. Instead of using INTERVAL, function called "add_months" needs to be used. Sample procedure has been uploaded for reference.
Feedback
Yes
No
Powered by
