很奇怪的TO_DATE

Question

最近在工作中发现一个奇怪的现象：执行以下SQLselect to_date(to_char(CSNY,'yyyy-mm-dd'),'yyyy-mm-dd'),to_char(CSNY,'yyyy-mm-dd'),to_date(CSNY),CSNY from GRXX where GRBH = '9000009500';结果：
1935-07-26 1935-07-26 2035-07-26 1935-07-26
其中：to_date(CSNY)，1935年的数据变成了2035小弟不才，那位大侠能够指点一下？谢谢！

Answer 1

eygle@SZTYORA> select to_date('01-03-27','yy-mm-dd') from dual;TO_DATE('01-03-27',
-------------------
2001-03-27 00:00:00eygle@SZTYORA> select to_date('11-03-27','yy-mm-dd') from dual;TO_DATE('11-03-27',
-------------------
2011-03-27 00:00:00eygle@SZTYORA> select to_date('21-03-27','yy-mm-dd') from dual;TO_DATE('21-03-27',
-------------------
2021-03-27 00:00:00eygle@SZTYORA> select to_date('31-03-27','yy-mm-dd') from dual;TO_DATE('31-03-27',
-------------------
2031-03-27 00:00:00eygle@SZTYORA> select to_date('41-03-27','yy-mm-dd') from dual;TO_DATE('41-03-27',
-------------------
2041-03-27 00:00:00eygle@SZTYORA> select to_date('51-03-27','yy-mm-dd') from dual;TO_DATE('51-03-27',
-------------------
2051-03-27 00:00:00eygle@SZTYORA> select to_date('61-03-27','yy-mm-dd') from dual;TO_DATE('61-03-27',
-------------------
2061-03-27 00:00:00eygle@SZTYORA> select to_date('71-03-27','yy-mm-dd') from dual;TO_DATE('71-03-27',
-------------------
2071-03-27 00:00:00eygle@SZTYORA> select to_date('81-03-27','yy-mm-dd') from dual;TO_DATE('81-03-27',
-------------------
2081-03-27 00:00:00eygle@SZTYORA> select to_date('91-03-27','yy-mm-dd') from dual;TO_DATE('91-03-27',
-------------------
2091-03-27 00:00:00eygle@SZTYORA> select to_date('99-03-27','yy-mm-dd') from dual;TO_DATE('99-03-27',
-------------------
2099-03-27 00:00:00

Answer 2

-- 只知道问人家，也不知道你CSNY字段的原数据是什么！

Answer 3

你用的哪个版本啊，to_date 函数的第二个参数可以省略吗？

Answer 4

原数据是DATE类型，我ORACLE是10G的。

Answer 5

之所以没有先用TO_CHAR,然后再TO_DATE，是因为存在一部分数据，如果用TO_CHAR(CSNY,'yyyymmdd')执行的结果是'0000-00-00',而原始数据是有值的。

Answer 6

oracle对2位年份的处理

Answer 7

select to_char(CSNY, 'yyyy-mm-dd'),
       to_date(CSNY),
       to_date(to_char(trunc(CSNY),'yyyy-mm-dd'),'yyyy-mm-dd'),
       CSNY  
from GRXX执行的结果如下，有三种不同的数据：1971-01-15 1971-01-15 1971-01-15 1971-01-15
1935-07-26 2035-07-26 1935-07-26 1935-07-26
0000-00-00 1964-05-07 1964-05-07 1964-05-07

Answer 8

select to_char(CSNY, 'yyyy-mm-dd'),
to_char(CSNY),
  to_date(CSNY),
  to_date(to_char(trunc(CSNY),'yyyy-mm-dd'),'yyyy-mm-dd'),
  CSNY   
from GRXX
你再加上to_char(CSNY)或许能看出点问题。就是说to_date(CSNY)的话里面的参数要先执行to_char(CSNY)转为字符串。

Answer 9

select to_char(CSNY, 'yyyy-mm-dd'),
  to_char(CSNY),
  to_date(CSNY),
  to_date(to_char(trunc(CSNY),'yyyy-mm-dd'),'yyyy-mm-dd'),
  CSNY   
from GRXX执行结果是：1971-01-15 15-1月 -71  1971-01-15 1971-01-15 1971-01-15
1935-07-26 26-7月 -35  2035-07-26 1935-07-26 1935-07-26
0000-00-00 00-0000-00  1964-05-07 1964-05-07 1964-05-07请帮忙看看，谢谢！

Answer 10

CSNY字段的原数据是什么？
CSNY字段的原类型是什么？-- 要查到底为什么：要从上面两个问题着手！

Answer 11

原数据类型是 DATE 型，原数据：
1971-01-15
1935-07-26
1964-05-07看不出来有什么差别啊？

Answer 12

eygle@SZTYORA> create table grxx(csny date);表已创建。eygle@SZTYORA> insert into grxx(csny) values(to_date('1971-01-15','yyyy-mm-dd'));已创建 1 行。eygle@SZTYORA> insert into grxx(csny) values(to_date('1935-07-26','yyyy-mm-dd'));已创建 1 行。eygle@SZTYORA> insert into grxx(csny) values(to_date('1964-05-07','yyyy-mm-dd'));已创建 1 行。eygle@SZTYORA> commit;提交完成。eygle@SZTYORA> select to_char(CSNY, 'yyyy-mm-dd'),
  2    to_char(CSNY),
  3    to_date(CSNY),
  4    to_date(to_char(trunc(CSNY),'yyyy-mm-dd'),'yyyy-mm-dd'),
  5    CSNY
  6  from GRXX;TO_CHAR(CSNY,'YYYY-M TO_CHAR(CSNY)                          TO_DATE(CSNY)       TO_DATE(TO_CHAR(TRU CSNY
-------------------- -------------------------------------- ------------------- ------------------- -------------------
1971-01-15           1971-01-15 00:00:00                    1971-01-15 00:00:00 1971-01-15 00:00:00 1971-01-15 00:00:00
1935-07-26           1935-07-26 00:00:00                    1935-07-26 00:00:00 1935-07-26 00:00:00 1935-07-26 00:00:00
1964-05-07           1964-05-07 00:00:00                    1964-05-07 00:00:00 1964-05-07 00:00:00 1964-05-07 00:00:00

Answer 13

这种数据用PL/SQL导出再导入，问题就不存在的。但是用DMP导出再导入，问题依然存在，我想会不会是数据库版本和以前不一致的问题？原始库的版本是8，现在我用的是10？

Answer 14

这个问题跟oracle存储Date型的机制有关系。
楼主你主要的错误在于对一个已经是Date型的字段做TO_DATE处理。
你自己试一下这个。
select to_date(to_date('1935-07-26','YYYY-MM-DD')) from dual;
结果跟你描述的一样，但是谁也没有说过这样用是正确的。

Answer 15

都已经解释了啊 ，按照9楼的试试看是因为你to_date()里面用了一个日期类型的参数，oracle首先将这个参数转换成string的（转换的格式将根据你数据库的默认格式），然后to_date()将这个字符串在转换成日期型的，但是在两次的转换的过程中因为格式不统一，所以会出现你上面的错误。
参考：
ALTER session SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
select to_char(CSNY, 'yyyy-mm-dd'),
to_char(CSNY),
  to_date(CSNY),
  to_date(to_char(trunc(CSNY),'yyyy-mm-dd'),'yyyy-mm-dd'),
  CSNY   
from GRXX
这样再查就不会有错了。