[6760] Invalid timestamp

Database
Fan

[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

4 REPLIES
Senior Apprentice

Re: [6760] Invalid timestamp

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)')
Fan

Re: [6760] Invalid timestamp

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

Enthusiast

Re: [6760] Invalid timestamp

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')
Senior Apprentice

Re: [6760] Invalid timestamp

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.