SQL Loader: how to load a date-time variable? 1

[SashaBuilder3]

Hi everybody,

To tell the truth I even don't know if this is a right place to ask my question. Anyway, the question is about the SQL Loader. I need to load a date-time field from a text file, the format of it is:

'dd/mmm/yyyy hh:mm' (ex. '23/Apr/1999 14:20').

Seems the Loader doesn't like this format. All my manuals deal with pure date loading without the time part.

Can anyone help with this? What should be the right mask for loading combined date-time values?


Thanks,

Alexandre

 

[karluk]

You have to specify a format mask in your control file. The field specification would be something like

date_col DATE(19) "dd/Mon/yyyy hh24:mi"

 

[rcurva]

Or,
if you are loading all your date columns with such format, you can specify from your loading script the environment variable NLS_DATE_FORMAT;

--Unix
export NLS_DATE_FORMAT="dd/Mon/yyyy hh24:mi"

--DOS
set NLS_DATE_FORMAT="dd/Mon/yyyy hh24:mi"

Once the variable is set, the data that will be loaded to all date columns will expect a format similar to the one you set in the environment.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[SashaBuilder3]

Karluk, Robbie,

Thank you very much, it works!


Alexandre