是因为to_date()里日期和格式不对应,还要注意当前的语言环境:SQL> select to_date('2005-10-10','yyyy-mon-dd') from dual;select to_date('2005-10-10','yyyy-mon-dd') from dualORA-01843: 无效的月份SQL> select to_date('2005-12-12','yyyy-mm-dd') from dual;TO_DATE('2005-12-12','YYYY-MM- ------------------------------ 2005-12-12 SQL> select to_date('2005-12月-10','yyyy-mon-dd') from dual;TO_DATE('2005-12月-10','YYYY-M ------------------------------ 2005-12-10SQL> alter session set nls_LANGUAGE = American;Session alteredSQL> select to_date('2005-Oct-10','yyyy-mon-dd') from dual;TO_DATE('2005-OCT-10','YYYY-MO ------------------------------ 2005-10-10
说半天还是白说,月份是可以用mon 的,使用的时候首先要确认你的NLS_LANGUAGE参数值,可以用下面语句查询: SQL> select * from V$NLS_PARAMETERS where PARAMETER = 'NLS_LANGUAGE';PARAMETER VALUE -------------- ------------------------- NLS_LANGUAGE SIMPLIFIED CHINESE可以看到现在NLS_LANGUAGE值为SIMPLIFIED CHINESE,所以用mon做日期转换必须这样写to_date('2005-12月-12','yyyy-mon-dd')然后在会话级别更改NLS_LANGUAGE SQL> alter session set nls_language = American;Session alteredSQL> select * from V$NLS_PARAMETERS where PARAMETER = 'NLS_LANGUAGE';PARAMETER VALUE ---------------------------------------------------------------- -------------NLS_LANGUAGE AMERICAN现在NLS_LANGUAGE值为AMERICAN,所以用mon做日期转换必须这样写to_date('2005-Oct-12','yyyy-mon-dd'),当然不知道月份简写,用全拼也可以 to_date('2005-October-12','yyyy-mon-dd')
------------------------------
2005-12-12
SQL> select to_date('2005-12月-10','yyyy-mon-dd') from dual;TO_DATE('2005-12月-10','YYYY-M
------------------------------
2005-12-10SQL> alter session set nls_LANGUAGE = American;Session alteredSQL> select to_date('2005-Oct-10','yyyy-mon-dd') from dual;TO_DATE('2005-OCT-10','YYYY-MO
------------------------------
2005-10-10
SQL> select * from V$NLS_PARAMETERS where PARAMETER = 'NLS_LANGUAGE';PARAMETER VALUE
-------------- -------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE可以看到现在NLS_LANGUAGE值为SIMPLIFIED CHINESE,所以用mon做日期转换必须这样写to_date('2005-12月-12','yyyy-mon-dd')然后在会话级别更改NLS_LANGUAGE
SQL> alter session set nls_language = American;Session alteredSQL> select * from V$NLS_PARAMETERS where PARAMETER = 'NLS_LANGUAGE';PARAMETER VALUE
---------------------------------------------------------------- -------------NLS_LANGUAGE AMERICAN现在NLS_LANGUAGE值为AMERICAN,所以用mon做日期转换必须这样写to_date('2005-Oct-12','yyyy-mon-dd'),当然不知道月份简写,用全拼也可以 to_date('2005-October-12','yyyy-mon-dd')