CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloperdatetime-format
Deepu298
Deepu298

Reputation: 75

Import date time into Oracle DB using SQL Developer format- "6/26/2018 12:41:00 PM"

I am trying to import data from CSV file in to Oracle Table.

One of the column is DATETIME, with an example value- "6/26/2018 12:41:00 PM" (With 2 spaces between date and time)

In Data Import Wizard --> Column Definition --> Data Type

I select "Data Type" as Timestamp.

What should select in the below fields- Size/Precision- ?? (I tried different sizes like 15, 23, 25,50 ) Format- ?? (I tried various formats- MM/DD/YYYY HH:Mi:SS AM, MM/DD/YYYY HH:Mi:SS)

Please advise.

Thanks!

Upvotes: 0

Views: 8531

Answers (3)

Deepu298
Deepu298

Reputation: 75

For my .csv file with the date value as "6/26/2018 12:41:00 PM" select the DATE (No timestamp) and used format- MM/DD/YYYY HH24:MI and it worked for me probably I have all 00 values in my time (SS).

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22457

Make sure it's stored as a date and not as a VARCHAR2.

Then put in a valid NLS_DATE_FORMAT, so we know how to properly insert the record. For your sample, this will do: MM/DD/YYYY HH:MI:SS AM

enter image description here

We validate the format, so you can see there's no warning next to the list of dates we are previewing below.

One might consider this question a duplicate of sorts...

But you're not dealing with the RAW INSERT statement, our GUI is, and this is how you tell us that same information.

Upvotes: 1

kanagaraj
kanagaraj

Reputation: 442

Try below format in database and also in excel

Import DB: while loading in table MM/DD/YYYY HH:MI:SS AM in sql developer using import.

CSV file : Also format the csv file date field with format using custom  m/d/yyyy h:mm:ss AM/PM

Upvotes: 1

Related Questions