| SQLLOADER FAILS WITH CCYYMMDD FORMAT [message #74125] |
Thu, 16 September 2004 06:28  |
Ask
Messages: 2
Registered: September 2004
|
Junior Member |
|
|
I have one table with one DATE column called Effective_Date .
scenario 1)
Assume that data file for loader is having date field as 20040909
Control file has effective_date columns as
Effective_Date Date(11) "YYYYMMDD"
----IT WORKS----
scenario 2)
Now assume that data file for loader is having date field as 21040909
control file effective_date columns as
Effective_Date Date(11) "CCYYMMDD"
-----IT DOESN'T WORK--------
ERROR In second scenario is ORA-01820
pls suggest some solution..Our input file is going to have the date in CCYYMMDD format only.
Thanks in advance!
|
|
|
|
|
|
| Re: SQLLOADER FAILS WITH CCYYMMDD FORMAT [message #74417 is a reply to message #74126] |
Tue, 07 December 2004 11:04  |
Richard Blake
Messages: 1
Registered: December 2004
|
Junior Member |
|
|
The two dates given in the original question are identical. The first is expressed as a 4-digit year, 2004, followed by month and day, 0909. The second is formatted as a 2-digit century, 21, followed by a 2 digit year, 04, followed by month and day. The question is Why does SQL Loader happily convert a character string to a date when the to_date format string is 'YYYYMMDD', but it chokes on another format?
BTW, the reason I found this is that I got the same error trying to convert a date into the date of the Monday of that week, e.g.
to_date(to_char(SYSDATE,'YYYY-WW'),'YYYY-WW')+1
|
|
|
|
| Re: SQLLOADER FAILS WITH CCYYMMDD FORMAT [message #74418 is a reply to message #74417] |
Tue, 07 December 2004 16:26 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are a few formats that are only usable as output formats in to_char and not usable as input formats with to_date. So, you would have to find some other workaround.
For the original problem that started this thread, in the sqlldr control file you could subtract a century (1200 months) by using add_months and -1200, like so:
Effective_Date "add_months (to_date (:effective_date, 'yyyymmdd'), -1200)"
For the monday of the current week you could use next_day to select the next monday, then subtract a week (7 days) to get the monday of the current week, like so:
scott@ORA92> select next_day (sysdate, 'mon') - 7 from dual
2 /
NEXT_DAY(SYSDATE,'MO
--------------------
06-dec-2004 18:28:59
scott@ORA92>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
