ora-01830错误：日期格式图片在转换整个输入字符串之前结束

oracl9i 数据库中

原有数据类型为string,

现在是timestamp

原有数据类似

2008-02-25 16:12:46.0

现在转换出现问题

insert into koalra.TB_USER_CERT
(CERT_ID,USER_ID,CERT_STATUS_ID,CERT_SN,CERT_NOT_BEFORE,
CERT_NOT_AFTER,CERT_SUBJECT_DN,CERT_EXTENSION,CERT_ISSUER_DN,CERT_ENTITY_ID,
CERT_SIGN_DATE,CERT_1,CERT_2,CERT_POLICY_NAME)

select CERT_ID,USER_ID,CERT_STATUS_ID,CERT_SN,to_date(CERT_NOT_BEFORE,'YYYY-MM-DD   HH24:MI:SS'),
to_date(CERT_NOT_AFTER,'YYYY-MM-DD   HH24:MI:SS'),CERT_SUBJECT_DN,CERT_EXTENSION,CERT_ISSUER_DN,CERT_ENTITY_ID,
to_date(CERT_SIGN_DATE,'YYYY-MM-DD   HH24:MI:SS') ,CERT_1,CERT_2,CERT_POLICY_NAME
from uaapra.TB_USER_CERT ;
commit;

ora-01830错误：日期格式图片在转换整个输入字符串之前结束 

insert into koalra.TB_USER_CERT
(CERT_ID,USER_ID,CERT_STATUS_ID,CERT_SN,CERT_NOT_BEFORE,
CERT_NOT_AFTER,CERT_SUBJECT_DN,CERT_EXTENSION,CERT_ISSUER_DN,CERT_ENTITY_ID,
CERT_SIGN_DATE,CERT_1,CERT_2,CERT_POLICY_NAME)

select CERT_ID,USER_ID,CERT_STATUS_ID,CERT_SN,to_date(substr(CERT_NOT_BEFORE,0,19),'YYYY-MM-DD   HH24:MI:SS'),
to_date(substr(CERT_NOT_AFTER,0,19),'YYYY-MM-DD   HH24:MI:SS'),CERT_SUBJECT_DN,CERT_EXTENSION,CERT_ISSUER_DN,CERT_ENTITY_ID,
to_date(substr(CERT_SIGN_DATE,0,19),'YYYY-MM-DD   HH24:MI:SS') ,CERT_1,CERT_2,CERT_POLICY_NAME
from uaapra.TB_USER_CERT ;
commit;

这样转换之后就可以了

有些人说

把sql语句输出作了以下的实验，发现是时间多了一个.0

后来的办法是先把这个时间转成to_char，再转成to_date，

但是我使用to_char没有成功。。。。。所以算了吧

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiaxiaorui2003/archive/2009/07/16/4354086.aspx