Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQLLOADER Date 1

Status
Not open for further replies.

jetechie

Technical User
May 23, 2006
15
US
Is there a method of modifying a date using sqlldr on insert?

My data is like the following:

20061002 TESTDATA

I would need to change teh 2006 to 2005 on loading.

Is there a way to parse the date?

Thanks for any ideas!
 
Hi, jetechie

You can use DECODE.

Regards,


William Chadbourne
Oracle DBA
 
How can you parse a date with a "decode"?
 
$ sqlplus myuser/mypass
SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jun 30 10:22:42 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production

SQL> desc tom
Name Null? Type
----------------------------------------- -------- ----------------------------
MYDATE DATE

SQL> select * from tom;

no rows selected

SQL>

$ type x.dat
20050505
20060101

$ type x.ctl
load data
infile 'x.dat'
append
into table tom
fields terminated by ',' optionally enclosed by '"'
(mydate date "yyyymmdd"
"decode(substr:)mydate,1,4),'2006','2005'||substr:)mydate,5,4),:mydate)")

$ sqlloader myuser/mypass control=x.ctl

SQL*Loader: Release 9.2.0.2.0 - Production on Fri Jun 30 10:25:41 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2
$
$ sqlplus capel/james

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Jun 30 10:26:27 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production

SQL> select * from tom;

MYDATE
---------
05-MAY-05
01-JAN-05

SQL>

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top