I am trying to import a Text File with several dates. 3 of them are in for format such as "2020-06-22 00:00:00" while one is in the format "2020-01-16 11:17:12.250000000".
I've changed the Date Order to "YMD", Date Delimiter to "-". This corrected the issue with the first 3 Dates, the the last one still shows as "" on the "Preview Results" screen.
What is the correct way for me to get this date format loaded in?
This probably doesn't help, but that last one is not a DATE it's a TIMESTAMP, probably, if I remember correctly, TIMESTAMP(9).
I'm literally about 300 miles from Toad and/or my databases right now, so is there a way you can tell the import to use a timestamp and not a date?
Sorry to be so vague, I've used Toad for decades (yes, I am that old), but I've almost never used the data import.
HTH
Cheers,
Norm. [TeamT]
Hi Steve,
I can get it to import into a table with DATE for the first format and TIMESTAMP for the second if I go into Toad's main options window, then click "Data grids" on the left, and "Data" under that. Set the Date format on the right to yyyy-mm-dd and time format to hh:mm:ss.
Seems like Toad ought to be able to figure that out if it's set in the data import window. I'll see if I can fix that for the next version.
-John
Thank you, that actually does help. I abandoned the import and tried it with SQL*Loader. Different issues, but after reading your replies, turned out it was very similar. Mentioning TIMESTAMP helped me out. The Database Column is DATE, but specified TIMESTAMP for the field coming in. That solved everything.
Hmmm. I rather suspect you will be losing accuracy as DATE columns are only accurate to the second level. A TIMESTAMP(9) is accurate to 0.000000001 of a second (Operating system allowing). Hopefully those columns are not being used for PK or UK contraints.
Cheers,
Norm. [TeamT]
No, several come in as dates. That is the only one with milliseconds. The date portion is all that is really needed.
That is what I did. All is good now. Thank you so much.