All Forums Database
Dan27 2 posts Joined 10/14
28 Oct 2014
[6760] Invalid timestamp

Hello
I'm trying to write a select statement on a database but keep getting a time stamp error. The field I'm trying to select is a TIMESTAMP(6) in the format of 14/11/2012 16:24:18.071000
Whichever way I've tried my WHERE date ='14/11... I get a timestamp error. I've tried casting as date but still get the issue.
Can anyone help?
Thanks

dnoeth 4628 posts Joined 11/04
28 Oct 2014

The easiest way to write a Timestamp literal is TIMESTAMP 'yyyy-mm-dd hh:mi:ss':

WHERE col = TIMESTAMP '2012-11-14 16:24:18.071000'

If you really need to use a different format you must use a CAST plus FORMAT:

WHERE col = CAST('14/11/2012 16:24:18.071000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')

 

Dieter

Dan27 2 posts Joined 10/14
28 Oct 2014

Thanks Dieter. That's working but I'm trying to return all the records for just a chosen date and not the time. Apologies if that wasn't clear in my original post. This is returning by date and time

vikas_yadav 19 posts Joined 09/12
28 Oct 2014

You need to bring both side to same data type.

WHERE CAST(col AS DATE) = CAST('14/11/2012' AS DATE FORMAT 'dd/mm/yyyy')

 

dnoeth 4628 posts Joined 11/04
28 Oct 2014

You can also write a DATE literal:

WHERE col = DATE '2012-11-14'
or
WHERE col = CAST('14/11/2012' AS DATE FORMAT 'dd/mm/yyyy')

There's no need for CAST(col AS DATE), the time portion is automatically stripped off when you compare a timestamp to a date. 

Dieter

You must sign in to leave a comment.