| loading date [message #198321] |
Mon, 16 October 2006 10:08  |
roopla
Messages: 52
Registered: May 2006
|
Member |
|
|
I have two fileds in my table one of them date filed. I am trying to populate this date field with constant value like following
But, it's erroring out by saying invalid month. Please advice
Options ( rows=100)
LOAD DATA
APPEND
INTO TABLE LOAD_TEST
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
var1 CHAR,
dt1 "to_char(sysdate,'mm') ||'/' || '25' || '/' || to_char(sysdate,'yyyy')"
)
|
|
|
|
| Re: loading date [message #198337 is a reply to message #198321] |
Mon, 16 October 2006 12:40  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
"dt1" column is of DATE datatype. Right?
If so, why are you trying to enter a CHARACTER into it? All that long TO_CHAR(this) || TO_CHAR(that) is a string. Default date format in your database doesn't match the one you guessed (and, as you've found out, you guessed wrong). It isn't mm/dd/yyyy but something else.
In order to fix it, either rely on Oracle's implicit conversion (first check default date format) -> which is a really bad idea, or take control over the process and insert a DATE, not CHARACTER. How? Like this:SQL> select to_char(sysdate, 'mm') || '25' || to_char(sysdate, 'yyyy') this_is_char
2 from dual;
THIS_IS_CHAR
------------
10252006
SQL> select
2 to_date(
3 to_char(sysdate, 'mm') || '25' || to_char(sysdate, 'yyyy'),
4 'mmddyyyy'
5 ) this_is_date
6 from dual;
THIS_IS_DATE
------------
25.10.06
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
