0

I am trying to use Oracle SQL Developer to import a CSV file to a table. One of the fields is in day/time format. An example of such a date is '9/15/1993 12:00:00.000 AM'. IN SQL Developer when it asks me what date format to use I enter MM/DD/YYYY HH:MI:SS AM but this creates a ORA-01855 error complaining about AM so I imagine something is wrong. Any ideas?

DanH
  • 51
  • 6

1 Answers1

1

It's probably more likely to be the milliseconds rather than the AM/PM indicator. Convert to a timestamp first then cast to date: String to date in Oracle with milliseconds

Community
  • 1
  • 1
Cade Roux
  • 83,561
  • 38
  • 170
  • 259
  • Are you saying this conversion needs to be done before importing to CSV? – DanH Mar 06 '17 at 20:32
  • @DanH I'm not sure what you mean; if you have control of the export which creates the CSV, you could always change that side instead. – Cade Roux Mar 06 '17 at 20:38
  • Thats the problem I have no control over the data that is in that CSV I have to work with what is given to me. I just put the data through notepad++ and did a mass replace of anything with ".000" to blank. Not the best solution but in the scope of the project I am working with it's ok. The data is uploading now. – DanH Mar 06 '17 at 20:41
  • @DanH - So, why did you do the string manipulation, instead of what Cade Roux suggested (which is a much better solution)? – mathguy Mar 06 '17 at 20:46
  • @mathguy From what I understand he is saying I should take care of the milliseconds BEFORE exporting to CSV. Again, I don't have that type of control. I'm stuck with a CSV that has milliseconds right at this moment and I can't change it. – DanH Mar 06 '17 at 20:49
  • @DanH - No, what the solution he linked to says to use `cast ( to_timestamp( ..., format_model) as date)` instead of `to_date(..., format_model)`. It's all Oracle, used in exactly the same place in the process. – mathguy Mar 06 '17 at 20:56
  • @DanH - unless... not sure how you are importing. You should be able to see the SQL generated by SQL Developer and make this edit to it. – mathguy Mar 06 '17 at 20:57
  • @mathguy I am using the built in importer. I don't see any sports for query manipulation like you are saying. It's all drop downs and selecting columns but no actual query. – DanH Mar 06 '17 at 21:30
  • @DanH In that case, you would probably have to use a TIMESTAMP destination column and a format of MM/DD/YYYY HH:MI:SS.FF3 AM if it lets you use that format there http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924 – Cade Roux Mar 06 '17 at 21:39
  • @Cade Roux I tried that and also got an ORA-01830 error. Someone suggested that before deleting their post and while it was a good idea I thought still produced that error. – DanH Mar 06 '17 at 21:42
  • @DanH - in SQL Developer, in the process of importing the data, the second step is to select the "Import Method". You selected `Insert`. Instead, from the drop-down window in that frame, you can select `Insert script`. At the end, instead of creating a table and importing the data, SQL Developer will generate an INSERT statement (and the CREATE TABLE statement) - that is where you can modify the INSERT statement before executing (with F9 or whatever you use to execute scripts). – mathguy Mar 06 '17 at 21:46
  • @mathguy ok I will use that for my next import if I can do the date conversion there it will save me tons of headaches. Thanks! – DanH Mar 06 '17 at 22:34