[ http://issues.apache.org/jira/browse/DERBY-896?page=comments#action_12364687 ]
Satheesh Bandaram commented on DERBY-896: ----------------------------------------- I think Derby currently doesn't support this casting... The documentation is wrong here and is left over from OLD cloudscape product. The exact same paragraph is found in Cloudscape documentation. Derby's casting system was reworked to bring it closer to standards.... Though I don't know exactly what the standards say about this particular conversion. Derby does support two builtin functions, time() and date() that can be used to extract time or date from timestamp types, as follows. values date(current_timestamp); I would like to mark this bug as NEW FEATURE and possibly open a documentation bug to remove that paragraph from documentation to reflect current Derby behavior. Any changes to Derby behavior could then be correctly added to documentation when it happens. > 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
