SQLLOADER Date 1

[jetechie]

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!

 

[oradba101]

Hi, jetechie

You can use DECODE.

Regards,


William Chadbourne
Oracle DBA

 

[jetechie]

How can you parse a date with a "decode"?

 

[taupirho]

$ 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(substrmydate,1,4),'2006','2005'||substrmydate,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>

 

[jetechie]

Thanks taupirho!