Sqlldr Date Issues [message #379534] |
Tue, 06 January 2009 23:45 |
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 #379569 is a reply to message #379557] |
Wed, 07 January 2009 01:25 |
|
Michel Cadot
Messages: 68694 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
|
|
|