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?

Solution 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

Solution 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.

Solution 3

I realize this thread is more than a year old but... Another option just to throw it in might 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 extra '?' thrown in to illustrate that you can even stick in a few extra '?'s. There is no complaint from Oracle if the digits represented by the '?'s do NOT have any corresponding character in the source time string. This might be helpful if you aren't sure of the precision of seconds you are receiving.

This option gives some flexibility to the format of "fractional seconds" from your source time. I do not know that this is actually documented anywhere.

Solution 4

I did 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

View more solutions

Comments

[acedanger]

You, kind sir, rock. I should have asked this question HOURS ago! (accepting this as the answer as soon as stack overflow will let me). Thank you, very much!

[northpole]

ya, the .FF will only work on the TO_TIMESTAMP function not the TO_DATE.

[Justin Cave]

Note that the TO_DATE call will only work if the portion of the string that identifies the fractional seconds happens to also be the number of seconds since midnight (which can only possibly happen 9 times an hour). If your times are always at midnight, that may not be a problem. But in general, you're going to get ORA-01838: seconds of minute conflicts with seconds in day errors. And the TO_TIMESTAMP call may cause performance problems if it forces implicit casts to take place that prevent indexes from being used.

[Justin Cave]

This TO_DATE call will only work if the portion of the string that identifies the fractional seconds happens to also be the number of seconds since midnight (which can only possibly happen 9 times an hour). If your times are always at midnight, that may not be a problem. But in general, you're going to get ORA-01838: seconds of minute conflicts with seconds in day errors.

[Dave Costa]

I like Justin's solution with SUBSTR better than the others using the .SSSSS format model, both for the reasons he gives in his comments on the other answers, and because it is more clear that the fractional second component of the time is being discarded.

[acedanger]

@Justin - Thank you for the explanation! It helps me to understand why I'm doing what I'm doing.

[kevin cline]

@Justin: One reason not to use setDate is the implicit timezone conversion that occurs between Java and the database.

[Justin Cave]

@kevincline - I'm not sure I understand. An Oracle DATE doesn't have a time zone associated. A Java java.sql.Date does. The setDate method of the PreparedStatement class converts the Java date to an Oracle date using the JVM's default time zone or the time zone specified in the Calendar you pass in. Either way seems preferable to throwing away the time zone information entirely in the sting that is passed in to Oracle.

[kevin cline]

@Justin: A java.sql.Date does not have a timezone. It's a millisecond offset from 00:00 GMT on 1/1/1970. The JVM timezone is not useful on a system providing services world-wide. And the database has a timezone as well. It's all incredibly sticky. See puretech.paawak.com/2010/11/02/…

[Justin Cave]

@kevincline - The database has a time zone but that time zone only comes in to play if we're talking about TIMESTAMP data types which is what that blog entry is discussing, not DATE data types. The only time zone that comes into play when you call setDate is the JVM's default time zone or the time zone specified in the Calendar you pass in. It clearly gets stickier when you want to interact with an Oracle TIMESTAMP column.

[Andrey Regentov]

No sir. Just change the first example to '00:00:01.2' (any !=1 instead of 2) and see the fail.