How to import Oracle DATE data back into Oracle table using data that was exported into data file via Toad?

Toad for Oracle
1

For simplicity let me reference database 1 (DB1) and database 2 (DB2).

  1. I used the CREATE TABLE script from DB1 and then created the same table in DB2

  2. The data type for the date columns are both DATE in both DB1’s table and in DB2’s table

  3. I used Toad to export the table data from DB1 into a “|” delimited text file

  4. When I attempt to use the Export file generated by Toad from DB1 to now import the data into DB2’s table using Toad I keep getting the same error: “Error: “8/24/2017 8:42:21 AM” is not a valid date value.” It seems like I should be able to use the Exported data from DB1 without modifying anything in advance since I am also using Toad to perform the Import using Toad also.

  5. If I modify that date to be “8/24/2017” then Toad is importing the test record BUT the date in the DB2 table is changed to be “8/24/2020”.

I have no idea what is going on, and Toad seems to be performing inconsistently in terms of date data. We use “Toad for Oracle” V12.1.0.22 here.

I was wondering if this is a common problem with a simple solution? Thanks in advance for any instructions/guidance given.

2

I read in another article to check the DB, Instance, and Session NLS_DATE_FORMAT parameter setting values. In DB1 and DB2 on our system each is the same “DD-MON-RR”/null/“DD-MON-RR” respectively; so from that point of view this shouldn’t be the source of the problem.

I then read in another forum article to set the “NLS_DATE_FORMAT” key on my machine (i.e. “DD-MON-RR” in my case), however I don’t have administrator privileges to create the key (we are not given admin privileges to the laptops we use) :

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE*YOUR_ORA_HOME*]
“NLS_DATE_FORMAT”=“dd.mm.yyyy hh24:mi:ss”

3

For clarity, we use “Toad for Oracle” V12.1.0.22

4

NLS_DATE_FORMAT is irrelevant here.

If you are using the import table data wizard, when you get to the “Specify Source Data Details” step, just make sure you specify the date format the same way it is shown in the file that you are importing from.

So if your date looks like: “8/24/2017 8:42:21 AM”, then…

  • Date Order = MDY
  • “4 digit years” should be checked.
  • Date Delimiter = /
  • Time Delimiter = :
  • Leading zeros in dates should be unchecked.
  • And if you are importing into timestamp columns (not dates), then check “apply format to timestamp fields”.
    If you still have problems, send me your table DDL and a small sample of the file that you are importing from, and I’ll help you get it working.

-John

John.dorlon@quest.com