[ http://issues.apache.org/jira/browse/DERBY-896?page=comments#action_12364719 ]
Bernt M. Johnsen commented on DERBY-896: ---------------------------------------- The SQL standard (1999) states (ch. 6.22): DATE may be cast to DATE and TIMESTAMP TIME may be cast to TIME and TIMESTAMP TIMESTAMP may be cast to DATE, TIME and TIMESTAMP when DATE cast to TIMESTAMP, the time 00:00:00 is used when TIME is cast to TIMESTAMP, current date is used (Note: TIME/DATE/TIMESTAMP types may also be cast to CHAR/VARCHAR/CLOB and vice versa) > Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' > to 'TIMESTAMP'." > -------------------------------------------------------------------------------------------- > > Key: DERBY-896 > URL: http://issues.apache.org/jira/browse/DERBY-896 > Project: Derby > Type: Bug > 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 > > 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 is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
