CODEWITHSUNDEEP
sqloracle-databasedate
Amr H. Abd Elmajeed
Amr H. Abd Elmajeed

Reputation: 1521

Oracle sql loader date format issue

I'm new to sql loader, and i'm having a problem with the date format.

Here is the input file record sample:

Y,1525039510,http-192.168.2.2,15-01-2011 00:00:032:728,64
Y,1525131958,http-192.168.2.2,15-01-2011 00:00:033:613,75

I'm having a problem with the fourth column, the date.

My current field entry is this:

start_time DATE "DD-MM-YYYY HH:MI

How do i parse the last part of the input 032:728 (seconds and milliseconds)

I tried SSS:FF3 and SS.FF3, no luck.

Upvotes: 1

Views: 5723

Answers (2)

user123664
user123664

Reputation:

You can read this into a timestamp field. The data looks a bit weird: 15-01-2011 00:00:033:613,75 but the conversion format should be dd-mm-yyyy hh[24]:mi:0ss:fff,ff Make sure that your nls settings are as expected.

Ronald.

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

You could treat the input as a string and transform it with a function. Something like this should work:

start_time CHAR to_date(substr(:start_time, 1, 20), 'dd-mm-yyyy hh24:mi":0"ss')

I had to drop the millisecond part since they are not part of the DATE datatype (to_date doesn't recognize the FF format).

Upvotes: 2

Related Questions