Problems with TO_DATE

Question

I have the following in my SQL where article. This works with an Oracle database. The sc_dt field sc_dt defined in db as a date field.

 sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD') 

throws the following error "date format picture ends before converting entire input string"

When I try to explain 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 really assume I need .FF to account for .0 in the from from line. I also tried .FF1 , .FF2 , ..., .FF9 with the same results (at this moment I grab onto a straw).

As far as I can see, the sc_dt field always contains the filled month / day / year (and not the hour / minute / second part).

I am debugging a java program that executes the above SQL as a prepared statement with a value of 2011-11-03 00:00:00.0 .

How can I get around this?

Answer 1

You need to use seconds after midnight. Something like:

 select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual 

Or that:

 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, such as sc_dt , will always have a day and a time component until the second. Depending on your query tool and its settings (typically the NLS_DATE_FORMAT session), it is possible that the time component is not displayed by default. However, you can see the time component by doing explicit TO_CHAR

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

Since DATE only saves time until the second, you cannot use fractional seconds in your format mask. Therefore, you will need to do something similar to extract only part of the string before fractional seconds. If you are not guaranteed that the string will always contain 19 characters to the decimal point, you can use INSTR to search 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 comes from a Java application, you are much better off using the correct data type. If you bind a Java date (java.sql.Date) using the setDate method in a prepared statement, rather than bind a string, then you do not have to deal with the string format in your SQL statement.

Answer 3

I understand that this topic is more than a year old, but ... Another option that could just be inserted could be:

 src_dt=select TO_DATE('2011-11-03 00:00:01.1234', 'YYYY-MM-DD HH24:MI:SS.?????') from dual; 

Note: there is an additional '?' to show that you can even insert a few extra "?". There is no complaint from Oracle if the numbers represented by "? DO NOT have a corresponding character in the original time string. This can be useful if you are not sure about the exact seconds you get.

This option gives some flexibility to the fractional second format in the original time. I do not know that this is somewhere documented somewhere.

Answer 4

I have done this:

 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS.?'; --Change the decimal ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'; 

And it worked for me

Answer 5

src_dt = select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual

I think the above should work if you just need a date output.