TO_DATE problems

Question

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 1

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

Answer 2

An Oracle DATE column like sc_dt will always have a day and a time component down to the second. Depending on your query tool and how it is configured (generally the session's NLS_DATE_FORMAT), it is possible that the time component isn't being displayed by default. You can, however, see the time component by doing an explicit TO_CHAR

SELECT to_char( sc_dt, 'YYYY-MM-DD HH24:MI:SS' ) 
  FROM table_name

Because a DATE only stores the time to the second, however, you cannot use fractional seconds in your format mask. So you would need to do something like this to extract just the portion of the string up to the fractional seconds. If you're not guaranteed that the string will always be 19 characters before the decimal point, you could use INSTR as well to look for the decimal point and take everything before that.

TO_DATE( substr('2011-11-03 00:00:00.0', 1, 19), 'YYYY-MM-DD HH24:MI:SS')

Since this is coming from a Java application, however, you're much better off using the correct data type. If you bind a Java date (java.sql.Date) using the setDate method on the prepared statement rather than binding a string, then you won't have to deal with the string format in your SQL statement.