CODEWITHSUNDEEP
oracle-databasedatetimestampstr-to-date
Pat
Pat

Reputation: 167

ORA-01830 Error - converting a string to a date object

I need to convert a string to a date object in oracle. Since all this is running inside a shell-script via the sql-loader (sqlldr) I can neither modify nor view the code. The only parameter I can set is the format-string for the conversion process.

The input string does always look the same: 'Mar 11 2015 7:37:53:060PM'

  1. Try: I created a format like this: 'Mon dd YYYY HH:mi:ss:ff3AM', but I couldn't run it, because I ran into this error message: "kgepop: no error frame to pop to for error 1821" (I assume this is realted to ORA-01821: date format not recognized) I assume this is because of the "ss:ff3AM", since I figured out that obviously the to_date function is not able to read milliseconds and AM/PM as an input parameter (please correct me if I am wrong, I am not 100% sure about this).

  2. Try: I created a format like this: 'Mon dd YYYY HH:mi:ss' and I hoped he might just cut off the rest of the input string but I ran into the ORA-01830 Error ("date format picture ends before converting entire input string")

Because of this I assumed, that the script does something like this:

to_date('MAR 11 2015  7:37:53:060PM','Mon dd YYYY HH:mi:ss');

Is there a way to tell the to_date method to cut off the last part of the input string via the format-string?

Every help will be appreciated !

EDIT

I unfortunately cannot use the to_timestamp method since I cannot modify the shell scripts functionality. So I have to use the to_date method :(

Upvotes: 1

Views: 3529

Answers (2)

kevinskio
kevinskio

Reputation: 4551

This works for me

SELECT to_TIMESTAMP('Mar 11 2015 7:37:53:060PM','Mon dd YYYY HH12:mi:ss:FF3AM') FROM dual;

yields

11-MAR-15 07.37.53.060000000 PM

Then you can truncate the extra empty precision

Upvotes: 1

Boneist
Boneist

Reputation: 23588

The DATE datatype doesn't support milliseconds. However, the TIMESTAMP datatype supports them just fine. Try TO_TIMESTAMP instead of TO_DATE?

Upvotes: 2

Related Questions