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.

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).

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.