Not all documented TIMESTAMP formats are properly recognized by TIMESTAMP() and
CAST() functions
------------------------------------------------------------------------------------------------
Key: DERBY-2203
URL: http://issues.apache.org/jira/browse/DERBY-2203
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.6
Reporter: Oleksandr Alesinskyy
Reference manual states
"Derby supports the following formats for TIMESTAMP:
yyyy-mm-dd hh[:mm[:ss[.nnnnnn]]]
yyyy-mm-dd-hh[.mm[.ss[.nnnnnn]]]"
There is following issue with this piece of documentation:
1.
>From this tatement follows that minutes, second and fractional seconds are
>optional.
In reality it is not so. Only fractional seconds part is treated as optional,
see below.
Note that CAST and TIMESTAMP functions report different errors (SQL states),
while, likely,
they shall be the same.
1.1 Hours only
values (TIMESTAMP('1969-10-01 00'));
SQL State = 22008 SQL Code = -1 SQL Message = '1969-10-01 00' ist ein
ungültiges Argument für die Funktion timestamp. Exception message =
java.sql.SQLException: '1969-10-01 00' ist ein ungültiges Argument für die
Funktion timestamp.
values (CAST('1969-10-01 00' as timestamp));
SQL State = 22007 SQL Code = -1 SQL Message = Die Syntax der
Zeichenfolgendarstellung eines datetime-Wertes ist fehlerhaft. Exception
message = java.sql.SQLException: Die Syntax der Zeichenfolgendarstellung eines
datetime-Wertes ist fehlerhaft.
1.2. Hours and minutes
values (TIMESTAMP('1969-10-01 00:00'));
SQL State = 22008 SQL Code = -1 SQL Message = '1969-10-01 00:00' ist ein
ungültiges Argument für die Funktion timestamp. Exception message =
java.sql.SQLException: '1969-10-01 00:00' ist ein ungültiges Argument für die
Funktion timestamp.
values (CAST('1969-10-01 00:00' as timestamp));
SQL State = 22007 SQL Code = -1 SQL Message = Die Syntax der
Zeichenfolgendarstellung eines datetime-Wertes ist fehlerhaft. Exception
message = java.sql.SQLException: Die Syntax der Zeichenfolgendarstellung eines
datetime-Wertes ist fehlerhaft.
1.3 Hours, minutes and seconds - HU-R-R-A-H!
values (TIMESTAMP('1969-10-01 00:00:00'));
Run successful
values (CAST('1969-10-01 00:00:00' as timestamp));
Run successful
2. Second of above mentioned formats exhibits exactly the same behavior.
I'm not sure if it is Derby bug or documentation bug, but, anyway, it shall be
corrected here or there.
--
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
