Home » RDBMS Server » Server Utilities » Date formatting in SQL Loader (10.2.0.1.0)
Date formatting in SQL Loader [message #379363] Tue, 06 January 2009 03:54 Go to next message
stowelle
Messages: 6
Registered: January 2009
Location: Bristol
Junior Member
Hi,

I'm currently trying to load some data into Oracle using the following SQLLDR Statement:

load data
infile 'S:\TPStats\BT Download\118247\2008\December\ERD_20081204.csv'
into table DAILYIMPORTTABLE
fields terminated by "," optionally enclosed by '"'
(text_col1 FILLER,
text_col2 FILLER,
CALLEDSERVICE,
text_col3 FILLER,
NOM_DATE "TO_DATE(:NOM_DATE,,'DD/MM/YYYY HH24:Mi:SS')",
TIME "TO_DATE(:TIME,'HH24:Mi:SS')",
Duration,
text_col4 FILLER,
text_col5 FILLER,
CLI,
BTID,
text_col6 FILLER,
text_col7 FILLER,
text_col8 FILLER,
text_col9 FILLER,
text_col10 FILLER,
text_col11 FILLER,
text_col12 FILLER)


The problem I'm having is with the field Nom_date the field in the file is in format dd/mm/yyyy. When I try to load it in the date in my file that is 04/12/2008 ie 4th December 2008 loads as 20-Aug-12. Please could somebody give me advice as its driving me mad!

Many Thanks,

Emma
Re: Date formatting in SQL Loader [message #379366 is a reply to message #379363] Tue, 06 January 2009 04:02 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try to change:
NOM_DATE "TO_DATE(:NOM_DATE,,'DD/MM/YYYY HH24:Mi:SS')",


to

NOM_DATE "TO_DATE(:NOM_DATE,'DD/MM/YYYY HH24:Mi:SS')",
Re: Date formatting in SQL Loader [message #379370 is a reply to message #379363] Tue, 06 January 2009 04:08 Go to previous messageGo to next message
stowelle
Messages: 6
Registered: January 2009
Location: Bristol
Junior Member
Sorry the code did say that originally I'd made a typo when I posted. I've tried it again without the typo and its still showing the wrong date,
Re: Date formatting in SQL Loader [message #379372 is a reply to message #379370] Tue, 06 January 2009 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without an example of your data it is hard to conclude anything.
Also post a description of your table (describe SQL*Plus command).

Regards
Michel
Re: Date formatting in SQL Loader [message #379376 is a reply to message #379372] Tue, 06 January 2009 04:26 Go to previous messageGo to next message
stowelle
Messages: 6
Registered: January 2009
Location: Bristol
Junior Member
Hi

In the course of asking your question I've answered my own I was looking at the data file in excel which was displaying the date on way, when I opened it in notepad it displayed it another way and has solved my problem. Thank you very much to take time out to help me though!

Emma
Re: Date formatting in SQL Loader [message #379378 is a reply to message #379376] Tue, 06 January 2009 04:31 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Glad you problem is solved. Thank you for the feedback.
Previous Topic: Import of a dump from a full database export
Next Topic: Sqlloader with skip columns
Goto Forum:
  


Current Time: Sat Dec 03 18:17:10 CST 2016

Total time taken to generate the page: 0.14990 seconds