Home » RDBMS Server » Server Utilities » to_date (sqlldr)
to_date [message #298802] Thu, 07 February 2008 14:44 Go to next message
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 #298805 is a reply to message #298802] Thu, 07 February 2008 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>How do I get past this.
Ensure the dates have leading zeros where appropriate.
Re: to_date [message #298806 is a reply to message #298802] Thu, 07 February 2008 15:13 Go to previous messageGo to next message
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 #298809 is a reply to message #298802] Thu, 07 February 2008 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Either manually edit the file(s) or modify the procedure which creates the file to consistently & properly format the dates.

Let me guess.
The data is coming from an Excel spreadsheet. Right?
Re: to_date [message #298820 is a reply to message #298802] Thu, 07 February 2008 16:04 Go to previous message
Littlefoot
Messages: 20896
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>
Previous Topic: Error during import using Impdp
Next Topic: Field in data file exceeds maximum length Error
Goto Forum:
  


Current Time: Tue Dec 06 10:40:18 CST 2016

Total time taken to generate the page: 0.08154 seconds