I have the following in my SQL where clause. This is running against an Oracle database. The sc_dt field is defined in the db as a date field.
sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD')
produces the following error "date format picture ends before converting entire input string"
When I try to account for the fractional seconds (.0 in this case) with the following, I get the following error.
sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD HH24:MI:SS.FF')
produces the following error "date format not recognized"
I'm really just assuming that I need the .FF to account for the .0 in the "from" string. I've also tried .FF1, .FF2, ..., .FF9 with the same results (I'm grasping at straws at this point).
As far as I can see, the sc_dt field always has the month/day/year portion populated (and not the hour/minute/second portion).
I'm debugging a java program which is executing the above SQL as a prepared statement with the 2011-11-03 00:00:00.0 value.
How can I get around this?
Answer
You need to use the seconds past midnight option. Something like:
select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual
Or This:
select TO_TIMESTAMP('2011-11-03 00:00:00.1', 'YYYY-MM-DD HH24:MI:SS.FF') from dual