[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

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5429?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13608875#comment-13608875 ] 

Steven Hawkins commented on TEIID-5429:
---------------------------------------

Thanks for raising this.  It looks like add_months will also handle the leap year case, so I'll map both the months and years interval to add_months.

> 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.



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