| to_date [message #298802] |
Thu, 07 February 2008 14:44  |
firsttimer
Messages: 3
Registered: October 2007
|
Junior Member |
|
|
I have a comma delimited file that contains a numeric date. I'm trying to use the to_date function to convert it. The date is formatted as mmddyyyy. My problem is the dates that contain the single digits months don't have the leading zero. The to_date function is taking the 1st two positions and trying to convert it as the month, resulting in an error. How do I get past this. I've tried running the 'errored' data thru sqlldr again, using mddyyyy as the date format, it tells me that isn't a valid format.
thanks
|
|
|
|
|
|
| Re: to_date [message #298806 is a reply to message #298802] |
Thu, 07 February 2008 15:13  |
firsttimer
Messages: 3
Registered: October 2007
|
Junior Member |
|
|
|
Thats my problem, how can I, thru sqlldr, assure that the date fields have leading zeros when they need them?
|
|
|
|
|
|
| Re: to_date [message #298820 is a reply to message #298802] |
Thu, 07 February 2008 16:04 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| firsttimer wrote on Thu, 07 February 2008 21:44 | The date is formatted as mmddyyyy. My problem is the dates that contain the single digits months don't have the leading zero.
|
Just to make sure I understood what you've said:
13 Dec 2007 = 12132007 (and this one is correct)
25 Jan 2008 = 1252008 (instead of 01252008)
3 Oct 2005 = 10032005 or is it
1032005
2 May 2003 = 5022003 or is it
522003
What I meant to say: how do dates where DAY is also one-digit number look like? Is there a leading zero for days or not?
If not, I'd say that you're in trouble as you can't tell whether there's a leading zero missing from a DAY or a MONTH (or both).
If, on the other hand, there is a leading zero for days, no problem - just LPAD the leading month value with a zero.
Here's an example:-- TEST.CTL
load data
infile *
replace
into table test
( datum date "mmddyyyy" "lpad(:datum, 8, '0')"
)
begindata
10132007 -- valid , 13 Oct 2007
1252008 -- invalid, 25 Jan 2008 SQL> create table test (datum date);
Table created.
SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Vel 7 23:03:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
SQL> select * from test;
DATUM
--------
13.10.07
25.01.08
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
