Re: Problems with SQL Loader Timestamp

Celia <member37093_at_dbforums.com> writes:

> Hi,
>
> there`s a problem with the data type date in SQL Loader. I'm trying to
> load a file with the following format:
>
>
>
> 2003-02-12 10:28:13.0, field2, ...
>
>
>
> I'd like to load the first field like a date not like a timestamp, but
> SQL Loader always gives me the same error: "ORA-01821: date format not
> recognized"
>
>
>
> I don't know what else I can do. I've tried tu put:
>
>
>
> FECHA DATE to_date(substr(:CMJE_FX_ENTRADA,1,19)
>
> ,'yyyy-mm-dd hh24:mi:ss')" terminated by '.', dummy FILLER
>

Try to convert the field first into a timestamp and then back to a date:

  to_char(to_timestamp( '2003-02-12 10:28:13.0',     'yyyy-mm-dd hh24:mi:ss.ff' ),'yyyy-mm-dd hh24:mi:ss')

Harald Received on Wed Aug 27 2003 - 06:49:10 CDT