Home » RDBMS Server » Server Utilities » SQL Loader query (Oracle Database 10g)
SQL Loader query [message #378857] Fri, 02 January 2009 03:28 Go to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi there,

Hoping someone can help me with this, potentially somewhat obscure, problem. I have some data in a text file separated by delimiters, one field of which is a date and time in the form:
ddmmyyyy hhmmss.
I would like to load it into my table in the form mmyy. The data type of the field in question is varchar2 4 bytes. Is there a way I can do this?

Thanks very much,

F

Report message to a moderator

Re: SQL Loader query [message #378860 is a reply to message #378857] Fri, 02 January 2009 03:34 Go to previous messageGo to next message
Frank Naude
Messages: 4587
Registered: April 1998
Senior Member
Yes, use the TO_CHAR and TO_DATE functions in your SQL*Loader control file.

Report message to a moderator

Re: SQL Loader query [message #378861 is a reply to message #378860] Fri, 02 January 2009 03:35 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
ok thanks, I'll give that a go.

Report message to a moderator

Re: SQL Loader query [message #378862 is a reply to message #378861] Fri, 02 January 2009 03:37 Go to previous messageGo to next message
Frank Naude
Messages: 4587
Registered: April 1998
Senior Member
SUBSTR() may also help. See "Can one modify data as the database gets loaded?" in the SQL*Loader FAQ.

Report message to a moderator

Re: SQL Loader query [message #378892 is a reply to message #378857] Fri, 02 January 2009 05:20 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Hi,

I've had a look at this but still a bit unsure of the syntax (as most of the examples I have come across relate to converting to a different date format, as opposed to converting to an integer). Would you happen to have an example?

Thanks,

F

Report message to a moderator

Re: SQL Loader query [message #378899 is a reply to message #378892] Fri, 02 January 2009 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If
Quote:
field of which is a date and time in the form:
ddmmyyyy hhmmss. I would like to load it into my table in the form mmyy

Then a simple SUBSTR will do the trick and there are several examples of applying a function in Frank's link, for instance in section "Can one modify data as the database gets loaded?". Read it carefully.

Regards
Michel

Report message to a moderator

Re: SQL Loader query [message #378906 is a reply to message #378899] Fri, 02 January 2009 08:29 Go to previous messageGo to next message
frog9697
Messages: 18
Registered: December 2008
Junior Member
Thanks very much, turned out to be more straightforward than I was expecting. For the record, in case anyone is interested, the following now works:

field_name "substr(:field_name, 3,2) ||''|| substr(:field_name, 7,2)",

Report message to a moderator

Re: SQL Loader query [message #378911 is a reply to message #378906] Fri, 02 January 2009 10:17 Go to previous message
Frank Naude
Messages: 4587
Registered: April 1998
Senior Member
Thanks for the feedback!

PS: The ''|| looks redundant to me. You can probably remove it.

Report message to a moderator

Previous Topic: How to write set define off statement in control file of sqldr
Next Topic: Different date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 19:55:17 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.