File contains date values as '09-01-2015 14:30:00'. But after importing to oracle table its showing all date values as '20-01-19 3:12:15.00000 PM'

• I have a csv file containing dates from 2015-2022 and time in 5 minutes interval date format as 09-01-2015 09:15:00+05:30.
• I need to change date format 09-01-2015 09:15:00+05:30 to 09-01-2015 09:15:00
• So I copied and pasted the data in notepad++ and removed all +05:30 from all rows and replaced in the csv file.
• Format became DD-MM-YYYY HH:MI:SS(09-01-2015 09:15:00) and then imported csv file into oracle table. At the time of import date format is shown as DD-MM-YYYY HH:MI:SS in both and excel file and sql developer.
• But after data imported into table, column values looks like this '20-01-19 3:12:15.000000000 PM 15:11:05.000'.I don't understand how sql developer assumed date format as.

Tried to find issue with below queries:

1. Tried to check the only year values. But year values itself is false in the table.

select to_char(datetime,'YYYY') from table;
0031

I tried with both date and timestamp datatypes for the column.

I don't understand the actual issue is in csv file or SQL developer. Can anyone help pls me.

1. original CSV file data

2. Removed +05:30 by replace all in notepad++ and then imported into table column with TIMESTAMP datatype. In Table Datetime values are displaying as:

3.

SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter LIKE 'NLS_%_FORMAT';

4.Imported through Data Import Wizard enter image description here

Solution:

As @MTO solution https://stackoverflow.com/a/78663198/21575280
I gave FXDD-MM-YYYY HH24:MI in wizard at the time import it worked.

Your image shows that the data is in the format:

09-01-2015 14:30
09-01-2015 14:35
09-01-2015 14:40
09-01-2015 14:45

and the target table format is YYYY-MM-DD HH24:MI:SS.

The format does not match the data!

---

You can see what happens in an SQL query:

SELECT TO_CHAR(
         TO_DATE(
           '09-01-2015 14:40',
           'YYYY-MM-DD HH24:MI:SS'
         ),
         'YYYY-MM-DD HH24:MI:SS'
       ) AS dt
FROM   DUAL;

Then the output is 0009-01-20 15:14:40 and the:

• day has been put into the year component;
• century has been put into the day component;
• year-of-century is in the hour component;
• hour is in the minutes component;
• minute is in the seconds component.

(Everything is wrong except for the month!)

---

To solve it, try setting the target format to FXDD-MM-YYYY HH24:MI. FX tells it to use that exact format, including spaces, hyphens and colons which without FX would be considered optional parts of the format model (you can omit the FX and just use DD-MM-YYYY HH24:MI but, just like your current format, it is likely to permissively accept values that are not in the exact format and that may cause issues and it is probably better to fail immediately with an error message than it is to find that you have data that looked okay but is actually invalid).

fiddle