CODEWITHSUNDEEP
oracle-databasedate-format
Sooraj
Sooraj

Reputation: 103

How to convert a Date String "09-JUN-14 12.00.00.000000000 AM" to date format in Oracle

I have an CSV file with Date specified as String as below format. I am not able to insert the date to Oracle Date Column as it shows "Date Format Not Recognized - Error" . Can someone help me to convert it.

Sample Dates in CSV

09-JUN-14 12.00.00.000000000 AM

21-MAR-16 12.00.00.000000000 AM

11-JUL-16 12.00.00.000000000 AM

26-OCT-15 12.00.00.000000000 AM

04-JAN-16 12.00.00.000000000 AM

Upvotes: 0

Views: 6865

Answers (2)

Aleksej
Aleksej

Reputation: 22959

Given that your strings have milliseconds, you need to convert them to timestamp:

to_timestamp( '09-JUN-14 12.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM', 'nls_language=English')

Also, given that the months are written th the format MON you'd better ensure to use English language in conversion. WHen you use the result to insert into a date column, only the date patr will be kept, and milliseconds will be lost:

SQL> create table tabDate (d date);

Table created.

SQL>
SQL> insert into tabDate
  2  select to_timestamp( '09-JUN-14 12.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM', 'nls_language=English')
  3  from dual;

1 row created.

Upvotes: 3

Gaj
Gaj

Reputation: 886

Try to follow this 

to_timestamp('09-JUN-2014 12.00.00.000000000 AM', 'dd-mon-yyyy hh.mi.ss.ff7 AM')

Upvotes: 0

Related Questions