SQL*Loader导入复杂的日期格式

最新推荐文章于 2023-03-29 16:13:46 发布

csdown1001

最新推荐文章于 2023-03-29 16:13:46 发布

阅读量303

点赞数

TEST.DAT内容如下
----------------
13436002514|CQ|D|Jun 28 2006 9:54:16:420AM||Jun 28 2006 9:54:16:420AM|0||0.00|
13436003898|CQ|D|May 17 2006 8:58:53:356PM||May 17 2006 8:58:53:356PM|0||0.00|
13436005288|CQ|D|Mar 20 2007 9:24:33:796AM||Mar 29 2007 1:51:51:153PM|0||0.00|
13436014844|CQ|U|May 2 2006 9:35:20:296AM||Sep 1 2006 9:12:51:830PM|0||0.00|

TEST.CTL内容如下
----------------
由于DATE型没有毫秒表示，所以你的文本里得毫秒被去掉了

LOAD DATA
INFILE 'TEST.DAT'
TRUNCATE
INTO TABLE USERS
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
MOBILENO,
BANKID,
AVLFLAG,
OPENTIME "DECODE(NULL,:OPENTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:OPENTIME,1,instrb(:OPENTIME,':',-1)-1)||' '||SUBSTRB(:OPENTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
STOPTIME "DECODE(NULL,:STOPTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:STOPTIME,1,instrb(:STOPTIME,':',-1)-1)||' '||SUBSTRB(:STOPTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
LASTTIME "DECODE(NULL,:LASTTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:LASTTIME,1,instrb(:LASTTIME,':',-1)-1)||' '||SUBSTRB(:LASTTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
FLAG,
PASSWORD,
TRANSLIMIT,
RIGHTS
)

create table USERS
(
MOBILENO CHAR(11) not null,
BANKID VARCHAR2(16) not null,
AVLFLAG CHAR(1) not null,
OPENTIME DATE not null,
STOPTIME DATE,
LASTTIME DATE not null,
FLAG FLOAT,
PASSWORD VARCHAR2(8),
TRANSLIMIT NUMBER(10,2),
RIGHTS VARCHAR2(32)
);

..--------------------------------------------------------------------------------

SQL>select * from users;

no rows selected

C:>sqlldr userid=test/test@gene control=test.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Thu May 31 14:49:58 2007

Commit point reached - logical record count 4

SQL> SELECT MOBILENO,
TO_CHAR(OPENTIME,'Mon dd yyyy hh12:mi:ss AM') OPENTIME,
TO_CHAR(STOPTIME,'Mon dd yyyy hh12:mi:ss AM') STOPTIME,
TO_CHAR(LASTTIME,'Mon dd yyyy hh12:mi:ss AM') LASTTIME
FROM USERS;

MOBILENO OPENTIME STOPTIME LASTTIME
----------- ----------------------- ----------------------- -----------------------
13436002514 Jun 28 2006 09:54:16 AM Jun 28 2006 09:54:16 AM
13436003898 May 17 2006 08:58:53 PM May 17 2006 08:58:53 PM
13436005288 Mar 20 2007 09:24:33 AM Mar 29 2007 01:51:51 PM
13436014844 May 02 2006 09:35:20 AM Sep 01 2006 09:12:51 PM

[@more@]

SQL*Loader

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94041/viewspace-917000/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94041/viewspace-917000/