1

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 :(

Pat
  • 167
  • 1
  • 3
  • 15

2 Answers2

2

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

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • Thanks for your fast response! Since I have to do all this inside a shell script, that I cannot modify, I unfortunately have to use the to_date function. But I tried the to_timestamp as well in another context with this input string and this works fine, so yes, you're totally right, but unfortunately I cannot solve it like this here. – Pat Apr 09 '15 at 14:55
  • If you can't modify the script, then your only alternative is to modify the data in the file, I guess! – Boneist Apr 09 '15 at 14:58
  • @Boneist, If I am not missing something here, why not just CAST the date as timestamp here? Pardon me if I am missing something, I am on the mobile-version right now :-( – Lalit Kumar B Apr 09 '15 at 15:15
  • I'm assuming that when the OP says "cannot modify the script", that also prevents them casting the string as a date or timestamp on the Oracle side. Which only leaves trimming the data in the file before attempting to load it via SQL*Loader, afaiui. – Boneist Apr 09 '15 at 15:17
  • Hmm, you are right. Saw OP's edited question now. We think alike, I mean, we are on same understanding. +1 from me (as I always upvote your answers, as I mostly find them perfect.) – Lalit Kumar B Apr 09 '15 at 15:23
  • This is true, I cannot influence any part of the conversion logic. I am only able to change the input or format string. So thank you for your answers! +1 – Pat Apr 10 '15 at 07:39
  • Since this answer (and the comments) clarify that there is no solution to solve this problem with the to_date function and since it's the simple truth that to_date won't work with milliseconds, I will mark this as the correct answer, thank you! – Pat Apr 14 '15 at 14:13
  • Yeah, sorry we couldn't provide you with a simple answer for your problem. I hope you manage to work out a way to fix the data in the file which isn't too complex/time consuming! – Boneist Apr 15 '15 at 12:41
1

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

kevinskio
  • 4,431
  • 1
  • 22
  • 36
  • Thanks for your fast response! Unfortunately I am forced to use the to_date method in this context, since I cannot modify the code, only the format string. – Pat Apr 09 '15 at 14:57
  • Then simple do `SELECT CAST(to_TIMESTAMP('Mar 11 2015 7:37:53:060PM','Mon dd YYYY HH12:mi:ss:FF3AM') AS DATE) FROM dual;` – Wernfried Domscheit Apr 09 '15 at 15:00