You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by Nicolas Dufour <ni...@neometsys.fr> on 2005/09/26 14:16:14 UTC

Derby dates

Hello

First of all, I would thank the work behind derby : it's a really good 
product :)

Now, I have a little problem to use it with datetime.
I have this error :

SQL Exception: The syntax of the string representation of a datetime 
value is incorrect
ERROR 22007: The syntax of the string representation of a datetime value 
is incorrect.

And I think that the values are for example in this format :
01/02/2004 00:00:00

I have looked at the docs, but I cant find a clear answer about this.
What is the correct datetime syntax ?

Thank you

Nicolas Dufour

Re: Derby dates

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.

>>>>>>>>>>>> Nicolas Dufour wrote (2005-09-26 15:15:23):
> Bernt M. Johnsen wrote:
> 
> >
> >{ts 'yyyy-mm-dd hh:mm:ss.f...'}
> >
> >Which should work for any conformat JDBC driver
> > 
> >
> so any other format like mm/dd/yyyy hh:mm:ss for example will not be 
> inserted right ???
> or is it a way to avoid this problem ?

Both the JDBC escape format
     {ts 'yyyy-mm-dd hh:mm:ss'}
and the Derby builtin
     timestamp('yyyy-mm-dd hh:mm:ss')
is conformant with ISO-8601

The escape format is conformant with the JDBC spec
The builtin is nearly conformant with the SQL standard 
(it should have been 
     timestamp 'yyyy-mm-dd hh:mm:ss'
....)

Any other date/time syntax will need to be converted by some means. 

Note however that there also are the classes
     java.sql.Date
     java.sql.Time
     java.sql.Timestamp
which may be used to insert dates/times into Derby
-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

Re: Derby dates

Posted by Nicolas Dufour <ni...@neometsys.fr>.

Bernt M. Johnsen wrote:

>>>>>>>>>>>>>Nicolas Dufour wrote (2005-09-26 08:16:14):
>>>>>>>>>>>>>                          
>>>>>>>>>>>>>
>>Hello
>>
>>First of all, I would thank the work behind derby : it's a really good 
>>product :)
>>
>>Now, I have a little problem to use it with datetime.
>>I have this error :
>>
>>SQL Exception: The syntax of the string representation of a datetime 
>>value is incorrect
>>ERROR 22007: The syntax of the string representation of a datetime value 
>>is incorrect.
>>
>>And I think that the values are for example in this format :
>>01/02/2004 00:00:00
>>
>>I have looked at the docs, but I cant find a clear answer about this.
>>What is the correct datetime syntax ?
>>    
>>
>
>{ts 'yyyy-mm-dd hh:mm:ss.f...'}
>
>Which should work for any conformat JDBC driver
>  
>
so any other format like mm/dd/yyyy hh:mm:ss for example will not be 
inserted right ???
or is it a way to avoid this problem ?

Thanks

>  
>
>>Thank you
>>
>>Nicolas Dufour
>>    
>>
>
>  
>

Re: Derby dates

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.

>>>>>>>>>>>> Nicolas Dufour wrote (2005-09-26 08:16:14):
> Hello
> 
> First of all, I would thank the work behind derby : it's a really good 
> product :)
> 
> Now, I have a little problem to use it with datetime.
> I have this error :
> 
> SQL Exception: The syntax of the string representation of a datetime 
> value is incorrect
> ERROR 22007: The syntax of the string representation of a datetime value 
> is incorrect.
> 
> And I think that the values are for example in this format :
> 01/02/2004 00:00:00
> 
> I have looked at the docs, but I cant find a clear answer about this.
> What is the correct datetime syntax ?

{ts 'yyyy-mm-dd hh:mm:ss.f...'}

Which should work for any conformat JDBC driver

> 
> Thank you
> 
> Nicolas Dufour

-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway