[teiid-issues] [JBoss JIRA] (TEIID-5429) timestampadd(SQL_TSI_MONTH, 1, "march-31-2000") results in ORA-01839: date not valid for month specified

[Ivan Semenov (JIRA)]

Ivan Semenov created TEIID-5429:
-----------------------------------

             Summary: timestampadd(SQL_TSI_MONTH, 1, "march-31-2000") 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.




--
This message was sent by Atlassian JIRA
(v7.5.0#75005)