我指的是整个表中的这个字段。都是这种类型需要转换,如出生年月csny中的10000条记录都是这种类型然后呢我是这样写的 update htjt set csny= (select to_char(to_date('csny','mm/dd/yy'),'yyyy/mm/dd') from dual) 但是出现了错误,错误是在要求数值地方找到非数字字符,这怎么处理呢?
select * from (select rowid no from htjt where translate(csny,'123456789','000000000')!='00/00/00')a,htjt b where a.no=b.rowid结果集就是格式不对的记录
首先把ORACLE的日期格式设置对了,再修改
已经在oracle数据库中类型为VARCHAR2类型了,只不过我是想把类似此种格式的01/25/88转换为1988/01/25,但是记录有上万条啊转换的时候会出现 update htjt set csny= (select to_char(to_date('csny','mm/dd/yy'),'yyyy/mm/dd') from dual) 错误是在要求数值地方找到非数字字符
update htjt set csny= to_char(to_date(csny,'mm/dd/rr'),'yyyy/mm/dd') where translate(csny,'123456789','000000000')='00/00/00')
from dual;
from dual;
from dual;
update htjt set csny= (select to_char(to_date('csny','mm/dd/yy'),'yyyy/mm/dd') from dual)
但是出现了错误,错误是在要求数值地方找到非数字字符,这怎么处理呢?
where a.no=b.rowid结果集就是格式不对的记录
update htjt set csny= (select to_char(to_date('csny','mm/dd/yy'),'yyyy/mm/dd') from dual)
错误是在要求数值地方找到非数字字符
to_char(to_date(STRING,'mm/dd/yy'),'yyyy/mm/dd')) FROM DUAL;测试:
SQL> SELECT DECODE (SIGN(substr('05/01/98',-2,2) - to_char(sysdate,'YY')),1,CONCAT('19',to_char(to_date('05/01/99','mm/dd/yy'),'yy/mm/dd')),
2 to_char(to_date('05/01/99','mm/dd/yy'),'yyyy/mm/dd')) FROM DUAL;DECODE(SIG
----------
1999/05/01SQL> SELECT DECODE (SIGN(substr('05/01/00',-2,2) - to_char(sysdate,'YY')),1,CONCAT('19',to_char(to_date('05/01/00','mm/dd/yy'),'yy/mm/dd')),
2 to_char(to_date('05/01/00','mm/dd/yy'),'yyyy/mm/dd')) FROM DUAL;DECODE(SIG
----------
2000/05/01