Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
![]() |
![]() |