Date format while importing table in sql developer

Question

I am trying to import details from a csv file which i am creating through BI Publisher query from a table.

In the query , I have a date formatted column:

effective_start_date

1951-01-01T00:00:00.000+00:00 
1901-01-01T00:00:00.000+00:00

Now when i am importing the entire table in excel. How should i import in sql developer in such a way that the date format comes as 'DD-MON-YYYY'

Right now few records for date are getting imprted in table as null !

Also my targer column format is not an editable screen in sql devleoper

How are you doing in the import - from the data import wizard? Dates don't have intrinsic formats so you're really asking how to convert the string you have into a date. Is the time/time zone part of all the values always "00:00:00.000+00:00"? — Alex Poole
– Alex Poole, Commented Mar 17, 2016 at 12:28
Hi @AlexPoole - From Oracle Fusion to csv through BI Publisher. From csv to table through sql devleoper — sreekem bose
– sreekem bose, Commented Mar 17, 2016 at 12:38
Yes, but exactly what are you doing in SQL Developer? Hmm, the import wizard doesn't like the format you'd need, YYYY-MM-DD"T00:00:00.000+00:00", getting "GDK-05021: date format not recognized", even though the generated insert is valid. Are you able to modify the CSV file creation to export just the YYYY-MM-DD part, if you aren't interested in the time? — Alex Poole
– Alex Poole, Commented Mar 17, 2016 at 12:39
actually while fetching the data only i converted this to to_char(effective_start_date,'dd-mon-yyyy') and then exported — sreekem bose
– sreekem bose, Commented Mar 17, 2016 at 12:41
CAST( TO_TIMESTAMP_TZ( value, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM' ) AS DATE ) — MT0
– MT0, Commented Mar 17, 2016 at 12:44

Alex Poole · Accepted Answer · 2016-03-17 15:29:26Z

Oracle Setup:

CREATE TABLE TEST_LOAD ( id INT, effective_start_date DATE );

CSV:

ID,EFFECTIVE_START_DATE
1,1951-01-01T00:00:00.000+00:00
2,1901-01-01T00:00:00.000+00:00

Import (SQL Developer 4.1.0.19):

In the connections panel, right-click on the table name and choose "Import Data..."
Browse to the csv file then click "Next >".
Select "Insert Script" as the insert method and click "Next >".
Click "Next >" to accept the default selected columns.
In the column definitions, EFFECTIVE_START_DATE will have a warning triangle next to it and will say "Data is not compatible with column definition...". Change the format to YYYY-MM-DD"T00:00:00.000+00:00" then click "Next >"
Click "Finish"
This should bring up a dialog saying "Task successful and sent to worksheet."
Optionally: Do a find/replace on the script replacing to_date$('.*?'), '.*?'$ with CAST( TO_TIMESTAMP_TZ( $1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM' ) AS DATE ) and select the regular expression option and do a "Replace all".
Run the script (press F5).

Output

SELECT * FROM TEST_LOAD;

ID EFFECTIVE_START_DATE
-- --------------------
 1 1951-01-01 00:00:00
 2 1901-01-01 00:00:00

(The format of the date will depend on your NLS settings.)

1 Answer 1