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?
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)')
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
You can also write a DATE literal:
WHERE col = DATE '2012-11-14'
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.