在使用PL/SQL Developler把01 Apr 2009转换本年的第几周时遇到ORA-01843

SQL> select to_char(to_date('01 Apr 2009', 'dd Mon yyyy'),'fmww') from dual;

select to_char(to_date('01 Apr 2009', 'dd Mon yyyy'),'fmww') from dual

ORA-01843: not a valid month

通过putty连到Linux主机运行上面的语句又可以运行

SQL> select to_char(to_date('01 Apr 2009', 'dd Mon yyyy'),'fmww') from dual;

TO

--

13

SQL> alter session set nls_date_format='dd Mon yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE

-----------

15 Jun 2009

查看关于nls_date_format和nls_date_langueage的设置

SQL> show parameter nls_date

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_format                      string

nls_date_language                    string

在PL/SQL Developler执行alter session set nls_date_format='dd Mon yyyy';

执行前后select sysdate from dual;无变化

设定nls_date_langeuage

alter session set nls_date_language='ENGLISH';

然后再在PL/SQL Developler中执行select to_char(to_date('01 Apr 2009', 'dd Mon yyyy'),'fmww') from dual;成功,到此问题解决.

也可以用下面的方式解决

 select to_date('01 Apr 2009','dd Mon yyyy','nls_date_language=English') from dual;
 select to_date('01 4る 2009','dd Mon yyyy','NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
在指定nls_date_langue为中文时要上面格式的写,American,Japanese等首字母大写即可,to_char也可以这样用如:

 select to_char(sysdate,'dd Mon yyyy','nls_date_language=English') from dual;