db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Oleksandr Alesinskyy (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-2203) Not all documented TIMESTAMP formats are properly recognized by TIMESTAMP() and CAST() functions
Date Fri, 29 Dec 2006 10:44:24 GMT
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

       

Mime
View raw message