Interval subtraction throws exception when calculating date in February

Interval subtraction throws exception when calculating date in February

book

Article ID: KB0083004

calendar_today

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”
 

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.
 
 

Issue/Introduction

Interval subtraction throws exception when calculating date in February
Powered by Wolken Software