[
https://issues.apache.org/jira/browse/TRAFODION-3071?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Wayne Birdsall closed TRAFODION-3071.
-------------------------------------------
> DATEDIFF function gives strange results when executed on interval data types
> ----------------------------------------------------------------------------
>
> Key: TRAFODION-3071
> URL: https://issues.apache.org/jira/browse/TRAFODION-3071
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Fix For: 2.3
>
>
> According to the SQL reference manual,
> [http://trafodion.apache.org/docs/sql_reference/index.html#datediff_function,]
> the DATEDIFF function is limited to DATE and TIMESTAMP operands.
> If one attempts DATEDIFF on INTERVAL data types, the errors are strange and
> non-intuitive. In some cases, DATEDIFF even succeeds on an INTERVAL data
> type. The following session output illustrates:
> {quote}>>drop table if exists t;
> --- SQL operation complete.
> >>create table t (c1 interval year, c2 interval year to month, c3 interval
> >>month);
> --- SQL operation complete.
> >>insert into t values (interval '11' year, interval '22-02' year to month,
> >>interval '33' month);
> --- 1 row(s) inserted.
> >>
> >>select DATEDIFF(MONTH, c1, c1) from t;
> *** ERROR[4037] Field MONTH cannot be extracted from a source of type
> INTERVAL YEAR(2).
> *** ERROR[4062] The preceding error actually occurred in function DATEDIFF.
> *** ERROR[8822] The statement was not prepared.
> >>select DATEDIFF(MONTH, c2, c2) from t;
> (EXPR)
> -----------
> 0
> --- 1 row(s) selected.
> >>select DATEDIFF(MONTH, c3, c3) from t;
> *** ERROR[4037] Field YEAR cannot be extracted from a source of type INTERVAL
> MONTH(2).
> *** ERROR[4062] The preceding error actually occurred in function DATEDIFF.
> *** ERROR[8822] The statement was not prepared.
> >>
> {quote}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
