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?

northpole · Accepted Answer · 2012-02-22T19:59:41.920

7

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

edited Feb 22 '12 at 19:59

answered Feb 22 '12 at 19:54

northpole

10,244
7
35
58

1

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! – acedanger Feb 22 '12 at 20:00
2

ya, the .FF will only work on the TO_TIMESTAMP function not the TO_DATE. – northpole Feb 22 '12 at 20:05
4

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 Feb 22 '12 at 20:08
No sir. Just change the first example to '00:00:01.2' (any !=1 instead of 2) and see the fail. – Andrey Regentov Feb 03 '17 at 09:09

score 5 · Answer 2 · answered Feb 22 '12 at 20:04

5

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.

answered Feb 22 '12 at 20:04

Justin Cave

227,342
24
367
384

2

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. – Dave Costa Feb 22 '12 at 20:26
@Justin - Thank you for the explanation! It helps me to understand why I'm doing what I'm doing. – acedanger Feb 22 '12 at 20:31
@Justin: One reason not to use setDate is the implicit timezone conversion that occurs between Java and the database. – kevin cline Feb 22 '12 at 20:55
@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. – Justin Cave Feb 22 '12 at 21:07
@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 http://puretech.paawak.com/2010/11/02/how-to-handle-oracle-timestamp-with-timezone-from-java/ – kevin cline Feb 23 '12 at 18:07
@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. – Justin Cave Feb 23 '12 at 18:13

score 3 · Answer 3 · edited May 12 '15 at 06:29

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.

score 1 · Answer 4 · answered Nov 16 '14 at 15:44

1

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

answered Nov 16 '14 at 15:44

Dominique Kande Vita

11
1

Teja · Answer 5 · 2012-02-22T20:02:55.037

0

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

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

edited Feb 22 '12 at 20:02

answered Feb 22 '12 at 19:54

Teja

13,214
36
93
155

2

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. – Justin Cave Feb 22 '12 at 20:10

TO_DATE problems

5 Answers5