Home » RDBMS Server » Server Utilities » Sqlldr Date Issues (10.2.0.2)
Sqlldr Date Issues [message #379534] Tue, 06 January 2009 23:45 Go to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
Input Data: Feb 7 2005 12:00:00:000AM

Control File: DueDate "to_date(:duedate,'mon dd yyyy HH24:MI:SS:ff')" ,

Using sqlldr it gives me "date format not recognized error"

Please Help
Re: Sqlldr Date Issues [message #379553 is a reply to message #379534] Wed, 07 January 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"FF" is a timestamp format model not a date format model.
You have to either use to_timestamp or ignore this part of your data.

Regards
Michel
Re: Sqlldr Date Issues [message #379557 is a reply to message #379553] Wed, 07 January 2009 00:52 Go to previous messageGo to next message
hparulkar
Messages: 15
Registered: August 2008
Location: India
Junior Member
If I remove FF portion it gives me following error

ORA-01830: date format picture ends before converting entire input string
Re: Sqlldr Date Issues [message #379569 is a reply to message #379557] Wed, 07 January 2009 01:25 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to tell Oracle there is something at the end you want to ignore (my default format is 'DD-MON-YY HH24:MI:SS'):
SQL> select to_date('Feb 7 2005 12:00:00:000AM','mon dd yyyy HH24:MI:SS') from dual;
select to_date('Feb 7 2005 12:00:00:000AM','mon dd yyyy HH24:MI:SS') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL> select to_date('Feb 7 2005 12:00:00:000AM','mon dd yyyy HH24:MI:SS":000AM"') from dual;
TO_DATE('FEB720051
------------------
07-FEB-05 12:00:00

1 row selected.

By the way, if you have "AM" in your data, I bet you use 12 hours time and not 24 one.
The format should be:
SQL> select to_date('Feb 7 2005 12:00:00:000AM','mon dd yyyy HH:MI:SS":000"AM') from dual;
TO_DATE('FEB720051
------------------
07-FEB-05 00:00:00

1 row selected.

Regards
Michel

Previous Topic: Sqlloader with skip columns
Next Topic: Load data into table but delete some reocrds using sql*loader
Goto Forum:
  


Current Time: Wed Dec 07 16:34:46 CST 2016

Total time taken to generate the page: 0.17393 seconds