Home » RDBMS Server » Server Utilities » loading date
loading date [message #198321] Mon, 16 October 2006 10:08 Go to next message
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 Go to previous message
Littlefoot
Messages: 20895
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>
Previous Topic: SQL*LOADER -941 Error and ORA-04043
Next Topic: SPECIAL CHARACTERS
Goto Forum:
  


Current Time: Mon Dec 05 21:25:46 CST 2016

Total time taken to generate the page: 0.12671 seconds