storing date and time value through SQL LOADER

4
votes

I am trying to write a control file for a sql loader. The source file to which is in fixed length format. I have date and time present in the source file like say , from position 17 to position 24 for date in CCYYMMDD format and from position 25 to 34 in HH:MM:SS24 format. I like to store this date and time value form source file in a date column in some table in 'YYYYMMDD HH24:MI:SS' format.

Can Anybody please help in telling how to achieve this in control file of sql loader?

Would following piece of code do? I doubt since there is no space between the date and time in the source file?

APPEND INTO TABLE target_table
TRAILING NULLCOLS
(
date_column POSITION(17:34) "TO_DATE(:DATE_TIME,'YYYYMMDD HH24:MI:SS')"
)

Any lead would be highly appreciated

1
sqloracledatetime-formatsql-loadercontrolfile
This should work. Remove the space though, the format should match the data in the file. Try it and see! - Gary_W

1 Answers

0
votes

Check Oracle's documentation BOUNDFILLER:

...
(date_column BOUNDFILLER POSITION(17:24), 
 time_column BOUNDFILLER POSITION(25:34), 
 date_n_time POSITION(1:1) 
             TO_DATE(:date_column || ' ' || :time_column, 'CCYYMMDD HH24:MI:SS'),
 ...
)

It allows you to define fields that do not correspond to any column.