Major Description
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:
>>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.
>>
Attachments
Issue Links
- links to
