to_date转成字符串时ORA-01843 not a valid month 问题分析

(注，本文三度易稿) 在开发Oracle SQL或PL/SQL程序过程中，我们常常遇到将字符串类型转换成DATE类型的需求。我们会使用to_date来实现这个转换需求。但有时候这种转换会出错，报ORA-01843:not a valid month。有时候这种转换在开发系统上测试时是正常的，部署到生产环境时却出这种错误。
例如，我们需要将字符串类型的字符串'19-JUN-01'转换为时间类型的对象。
这个字符串是常见的英文环境中的时间表达方式"天-月-年",其中"月"使用英文简写。所以使用to_date(xx,'dd-mon-yy')转换它，转换格式是'dd-mon-yy'。
但是，在实际转换操作时，转换失败，如下所示：

SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;
select  To_Date('19-JUN-01','dd-mon-yy') from dual
ORA-01843: not a valid month

 
出错信息显示"月"所对应的数据无效。
就是说，to_date函数在这个系统中不能识别"JUN".这是英文的月简写。


如果换成中文的表达方式，如'19-10月-01'。则系统就能正确转换。如下所示：

SQL>  select  To_Date('19-10月-01','dd-mon-yy') from dual;
TO_DATE('19-10月-01','DD-MON-Y
------------------------------
2001-10-19

 
这个问题的本质是系统不能识别英文的月简写，而能识别中文。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1735466 )
 
Oracle系统的语言配置主要保存在V$NLS_PARAMETERS数据字典视图中。查询该视图关于语言的设置值。如下：

SQL> select * from v$nls_parameters where parameter like '%DATE%';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE

 
证实数据库系统是语言环境是简体中文，这也是数据库实例级别的配置。

如果需要使To_Date('19-JUN-01','dd-mon-yy') 正常操作，只需要修改一下NLS_DATE_LANGUAGE的值，修改为AMERICAN，就能识别。

对于开发人员而言，实例级的语言设置参数NLS_DATE_LANGUAGE是不能修改的，但你可以修改会话级的。只要将你连接上数据库实例的会话中该参数值修改为AMERICAN，也是能转换的。

SQL> alter session set nls_date_language='american';
 
Session altered
 
SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;
 
TO_DATE('19-JUN-01','DD-MON-YY
------------------------------
2001-6-19
 
SQL> 

 

这样就可以使用了。

如果实例级的NLS_DATE_LANGUAGE 参数值是AMERICAN，而需要将'19-10月-01'转换成DATE时，也可以相同的操作。


这个先修改会话级的参数配置，再使用to_date转换操作，在开发过程中有时候会比较麻烦。这里还有一个方法，我们使用一个to_date也能搞定。如下所示：

 

SQL>  select  To_Date('19-10月-01','dd-mon-yy','NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
 
TO_DATE('19-10月-01','DD-MON-Y
------------------------------
2001-10-19
 
 
SQL> select  To_Date('19-JUN-01','dd-mon-yy','NLS_DATE_LANGUAGE = American') from dual;
 
TO_DATE('19-JUN-01','DD-MON-YY
------------------------------
2001-6-19

 

从以上可以看出，to_date的函数还是挺复杂的，也是很强大的。

 

 

我再提供一种解决方法，修改一下连接数据库的应用服务器的操作系统的环境变量NLS_LANG的配置，也可以解决这个问题。

如客户端是windows系统，修改它的环境变量NLS_LANG操作如下：

 

D:\>set NLS_LANG=american_america.zhs16gbk

D:\>sqlplus htzq/htzq@servdb4_192.168.15.209
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 27 15:47:35 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
------------
27-NOV-12

SQL> select  To_Date('19-JUN-01','dd-mon-yy') from dual;

TO_DATE('19-
------------
19-JUN-01

 
到此，我们解释一下to_date的转换过程。（个人见解，不一定对）

to_date函数转换字符串为时间类型时，先读取客户端操作系统环境变量NLS_LANG的配置，如果客户端没有此项配置，那么就从数据库实例中取v$nls_parameters的NLS_DATE_LANGUAGE的配置值，形成会话级级NLS参数配置值。
这个值保存在UGA（可能是PGA的一部分）中。所以，我们可以使用alter session来修改它的参数配置值。

延伸一下，我们甚至还可以将'dd-mon-yy'也省略掉。这个省略掉的参数取的是v$nls_parameters的NLS_DATE_FORMAT参数的值。

 

NLS_DATE_LANGUAGE  derived from NLS_LANGUAGE, specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.

 

 

SQL> select * from v$nls_parameters where parameter like '%DATE%';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE
 

SQL> alter session set nls_date_language='american';

Session altered.

SQL> select * from dual where sysdate>TO_DATE('19-JUN-01');

D
-
X

SQL> alter session set nls_date_language='SIMPLIFIED CHINESE';

Session altered.

SQL> select  * FROM DUAL WHERE SYSDATE >To_Date('19-10月-01');

D
-
X

 

我们直接使用to_date，将字符串'19-JUN-01'和'19-10月-01'转换成date类型的数据。
你看，它可以直接和sysdate进行比较的。

更进一步，其实oracle中连to_date都可以省略掉。

 

SQL> select * from dual where sysdate>'19-JUN-01';

D
-
X

 

我们甚至可以直接使用'19-JUN-01'和sysdate进行比较，因为Oracle在后台给你转换，但不推荐这种方法。

评论

3 楼 mikixiyou 2012-11-27  

masuweng 写道

楼上写的不错

小技巧，不足挂齿。

2 楼 masuweng 2012-11-27  

楼上写的不错

1 楼 caijingbin0207 2012-11-27