RE: sql loader question

Ryan,

Have you tried the following?

my_date "to_date(nvl(:my_date,'9999-01-01'),'YYYY-MM-DD')"

Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Monday, December 17, 2007 12:14 PM To: oracle-l_at_freelists.org
Subject: sql loader question

I rarely use sql loader.

I need to account for nulls and set them to January 1, 9999

I am getting errors when I try to use a to_date function in sql loader even though I have seen examples just like this on the web. This is 10.2

my_date date "to_date(:my_date,'YYYY-MM-DD')"

I get: ORA-01821: date format not recognized

The following works:

my_date date "YYYY-MM-DD"

I am trying to get to something like this:

my_date date
"decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_dat e,'YYYY-MM-DD')"

I dont think nvl will give me what i want since I still need an "else" mask. At this point, I can't get the basic syntax right.

Here is the header part of the control file

LOAD DATA
CHARACTERSET UTF8
INFILE "load.data" "var 7"
APPEND INTO TABLE my_table FIELDS TERMINATED BY ' ' ENCLOSED By "'" AND "'"
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 17 2007 - 12:18:11 CST