[teiid-issues] [JBoss JIRA] (TEIID-5429) timestampadd(SQL_TSI_MONTH, 1, to_date('2018/03/31', 'yyyy/mm/dd')) results in ORA-01839: date not valid for month specified

timestampadd(SQL_TSI_MONTH, 1, to_date('2018/03/31', 'yyyy/mm/dd')) results in ORA-01839: date not valid for month specified ---------------------------------------------------------------------------------------------------------------------------- Key: TEIID-5429 URL: https://issues.jboss.org/browse/TEIID-5429 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 11.x Reporter: Ivan Semenov Assignee: Steven Hawkins There is a known problem with Oracle's intervals: {code:sql} select to_date('2018/03/31', 'yyyy/mm/dd') + (INTERVAL '1' MONTH(2)) from dual {code} results in {{ORA-01839: date not valid for month specified error}} As an alternative, ADD_MONTHS function could be used . {code:sql} select ADD_MONTHS(to_date('2018/03/31', 'yyyy/mm/dd'), 1) from dual {code} It is not defined in the functions spec how timestampadd with SQL_TSI_MONTH should behave in some cases but anyway no error should happen. Currently "interval" - inflicted Oracle errors are transmitted for timestampadd invocations.