Re: Problems with SQL Loader Timestamp

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 27 Aug 2003 14:05:55 -0700
Message-ID: <4b5394b2.0308271305.16a45f3@posting.google.com>

Celia <member37093_at_dbforums.com> wrote in message news:<3290228.1061979965_at_dbforums.com>...
> 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"
>

sounds okay, this data doesn't match the default.
>
>
> 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
>
>
>
> but it doesn't work either.

What error do you get here? I don't think you get the same one. I don't have my manual handy, but you may have quoted that expression incorrectly?? (I see only one double quote (") character.) Your expression is trying to put part of the field "CMJE_FX_ENTRADA" into column FECHA. So you load this data into two columns? (one as text and one as date?) Do you really need the substring operation?

>... I don't mind at all if my field finally is a

> date or a timestamp, the only I want is SQL Loader could read it.
>
>
>
> Thanks

failsafe position:
load the date into the text field, then do the conversion after the load (in an UPDATE statement) -

UPDATE tableX set FECHA=to_date(substr(CMJE_FX_ENTRADA,1,19),

                                'yyyy-mm-dd hh24:mi:ss') ;

But I really suspect you are not showing the exact code that fails. You may have copied and pasted incorrectly. Received on Wed Aug 27 2003 - 16:05:55 CDT