RE: sql loader question
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <ryan_gaffuri@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Dec 2007 13:18:11 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
ryan_gaffuri@xxxxxxxxxxx
Sent: Monday, December 17, 2007 12:14 PM
To: oracle-l@xxxxxxxxxxxxx
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
"'"
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
