You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2014/02/13 10:57:19 UTC
[jira] [Updated] (DERBY-896) Cast date to timestamp results in
"ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."
[ https://issues.apache.org/jira/browse/DERBY-896?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-896:
-------------------------------------
Attachment: allow-casts.diff
On the face of it, the attached patch (allow-casts.diff) seems to be sufficient. The casts seem to work, and the missing date or time component is filled with the correct values as described by Bernt:
{noformat}
ij> values cast(current time as timestamp), cast(current date as timestamp);
1
-----------------------------
2014-02-13 10:32:32.0
2014-02-13 00:00:00.0
2 rows selected
{noformat}
I'll run the regression test suite to see if it breaks something. If everything looks good, I'll write some new tests for the casts and try to get it checked in.
It would be good to put this issue to rest, at last...
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-896
> URL: https://issues.apache.org/jira/browse/DERBY-896
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Environment: --------- Derby Information --------
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
> [D:\Programme\Derby\bin\lib\derby.jar] 10.1.2.1 - (330608)
> [D:\Programme\Derby\bin\lib\derbytools.jar] 10.1.2.1 - (330608)
> Reporter: Oleksandr Alesinskyy
> Labels: derby_triage10_5_2
> Attachments: allow-casts.diff, w.sql
>
>
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'.",
> e.g. in ij:
> select cast(cast ('2006-01-01' as date) as timestamp) from sys.sysschemas;
> or
> select cast (current_date as timestamp) from bradi_dat.lpaip01;
> The similar issue exists for time ("ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'.",
> Documentation clearly states that these conversions are allowed, see table 1 in a description of CAST function in the Derby Reference Manual and comment about "Conversion of date/time values" below that table (see below as well).
> Conversions of date/time values
> A date/time value can always be converted to and from a TIMESTAMP. If a DATE is
> converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always
> 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is
> set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP
> is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is
> converted to a TIME, the DATE component is silently truncated.
>
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)