[jira] [Commented] (DERBY-896) Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."

["Lukas Eder (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-896?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13899283#comment-13899283 ] 

Lukas Eder commented on DERBY-896:
----------------------------------

I can reproduce this issue with

{code}
select current_date as "d", cast(current_date as timestamp) as "ts" from "SYSIBM"."SYSDUMMY1"
{code}

Note that according to the Derby manual, this should be possible:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html

(I know, I'm just repeating what Oleksandr already said)

Just to get some new life in this issue, which is probably easy to fix, which pulls its own weight, and which is a bit surprising :-)

> 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: 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)