I have exported data to a csv in Oracle SQL Developer and one of the columns are of an Oracle Date datatype. In opening up Excel I see the Date column represented as 15-MAY-01 12.00.00.000000000 AM.
I am now going through the Data Import Wizard to create a new table and import the data. When I get to Step 3 of 4 it reads the data and assigns it a VARCHAR2. I want the column to be Date or Timestamp. In this case I choose timestamp and for format entered DD-MON-YY hh.mi.ss.fffffffff AM.
When I go to Step 4 of 4 to and click the Verify button I get:
"Verifying if the Date columns have date formats" Date columns DOT_LOGN_EFDT, have invalid or null date formats.
I am using Oracle SQL Developer 220.127.116.11 Build 63.73.
Any idea what I put in the format window to read the excel value of 15-MAY-01 12.00.00.000000000 AM correctly?
I am not able to reproduce your problem. When I export a date column, I do not get the time portion. This is the expected behavior. When re-importing the date, I therefore have no problem. I have tried manipulating the tools->preferences->database->nls->date format mask to include the time (and fractional seconds), but export still only exports the date portion. The fractional seconds is not valid for a date field, which explains the import error you are getting. The question remains as to why the exported data has the time. Can you describe your exact steps for doing the export?
On further research, I needed to restart sqldeveloper to pickup changes in the NLS_DATE format. If I have a valid date format set which includes the time without fractional part, then export/import works. Please check your NLS_DATE format to ensure that it does not have a fractional part included.
Hi Keith Brockman,
Have you solved this issue? I am encountering the same problem as yours. I cannot import the dates from excel with timecomponent.
Sample exported data: '31-MAY-09 10.44.22.000000000 AM'
In the data import wizrd of SQL developer, what should I enter as the date format mask?
Edited by: user13038177 on May 3, 2010 10:48 PM
You can actually import a time component into a DATE field, but only without the millisecond fraction, so in your case you should remove the ending .000000000 from your source file, or modify your DATE field to a TIMESTAMP and then run the import again.