Re: sql*loader and dates

Hi,

Try to use:

last_advance_date DATE 'YYYYMMDD' "decode(:last_advance_date,'0',sysdate, :last_advance_date)

Sorry, I have not time to test it.

Best regards, Dmitry

--
http://www.ispirer.com - Database migration tools and services for Oracle
and IBM DB2.


<hanton32_at_hotmail.com> wrote in message news:9bf7l5$8j4$1_at_news.netmar.com...


>

> I am loading a date column from a file that does not always have a date.

> eg.

> last_advance_date

> 20000101

> 20010303

> 0

> 0

> 20010302


>

> in my control file have coded the following mask for this column

> (last_advance_date date:

>

> last_advance_date "decode(:last_advance_date,'0',sysdate,

> to_date(:last_advance_date,'YYYYMMDD'))"

>

> so that when a '0' is found in loaded column it will replace it with

 SYSDATE.


> But I am getting the following error :

>

> Record 1: Rejected - Error on table FLMTG1, column LAST_ADVANCE_DATE.


> ORA-01843: not a valid month

>

> I have also tried defining last_advance_date as varchar2(8) but I get the

 same


> error.

>

> any help appreciated.

>

>  -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the

 eb  -----


>   http://newsone.net/ -- Free reading and anonymous posting to 60,000+

 groups


>    NewsOne.Net prohibits users from posting spam.  If this or other posts

> made through NewsOne.Net violate posting guidelines, email


abuse_at_newsone.net