Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel Date Field Import Problem in Oracle SQL Developer

I have an Excel file (which has data imported from Oracle 10G Database) one of the fields is a Date Filed which has values like 28-JAN-11 03.25.11.000000000 PM ( Date field is Oracle Time Stamp(6) in Database )

When I am trying to Import that Excel file to another Oracle 10 G database (for another database/application), I get an error because the data field is not being recognized by Oracle 10G --> Import is being done by ORACLE SQL Developer (Table (field) has TIMESTAMP(6) as the datatype)

How can I import that field? For time being I made the TIMESTAMP to VARCHAR2 and its working but I could not convert that to Date field again in C# CODE ( it says not a valid date type).

like image 252
msbyuva Avatar asked May 20 '11 02:05

msbyuva


People also ask

How do I insert date in YYYY MM DD in Oracle?

The TO_DATE function allows you to define the format of the date/time value. For example, we could insert the '3-may-03 21:02:44' value as follows: insert into table_name (date_field) values (TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')); Learn more about the TO_DATE function.

How do I change the date format in Oracle SQL Developer?

From Oracle SQL Developer's menu go to: Tools > Preferences. From the Preferences dialog, select Database > NLS from the left panel. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field. Save and close the dialog, done!


2 Answers

You likely need to change your SQL Developer settings.

Tools->Preferences->Database->NLS

Modify the Timestamp Format field to conform to the data format in your Excel spreadsheet. Not how it appears in the first database, but what it looks like in your spreadsheet. For example, in my instance, the data in Excel was:

ID  DT
1   05/19/2011 10:16 PM

I changed the format in preferences to MM/DD/YYYY HH:MI AM

and was able to import successfully, after initially getting the same error you reported before changing preferences.

You may want to change the way your data is imported into Excel originally if you need more precision from your time values.

like image 113
DCookie Avatar answered Sep 20 '22 09:09

DCookie


During the import wizard process (column definition screen), select the date type field on the left panel under 'Source Data Columns' and type in the expected format in the 'Format' text field. For example, MM/DD/YYYY or whatever the date format is in your import file for that field.

like image 44
Palani Chockalingam Avatar answered Sep 21 '22 09:09

Palani Chockalingam