ORA-01846：周中的日无效

SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;

提示错误：ORA-01846：周中的日无效

 

在Oracle是提供了next_day求指定日期的下一个日期.

语法 : next_day( date, weekday )

date is used to find the next weekday.
weekday is a day of the week (ie: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY)

可用于:
Oracle 9i, Oracle 10g, Oracle 11g
For example:

next_day('01-Aug-03', 'TUESDAY') would return '05-Aug-03'
next_day('06-Aug-03', 'WEDNESDAY') would return '13-Aug-03'
next_day('06-Aug-03', 'SUNDAY') would return '10-Aug-03'

但是在执行如下命令时出错,错误提示为不合法的 month,但实际上单词本身并没有错,而是环境问题:

SQL> select next_day('01-Aug-03', 'TUESDAY') from dual;
select next_day('01-Aug-03', 'TUESDAY') from dual
                *
ERROR at line 1:
ORA-01843: not a valid month

分析问题:

首先查询当前日期语言 NLS_DATE_LANGUAGE 值.
SQL>select * from v$nls_parameters;
PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE
NLS_TERRITORY                                                    CHINA
NLS_CURRENCY                                                     ￥
NLS_ISO_CURRENCY                                                 CHINA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE
NLS_CHARACTERSET                                                 ZHS16GBK
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                ￥
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE

经过查询,发现NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE

解决办法:

1 . 改写命令
SQL>select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA
--------------
23-3月 -09

2. 修改当前会话NLS_DATE_LANGUAGE值为AMERICAN

SQL> alter SESSION set NLS_DATE_LANGUAGE=AMERICAN ;

Session altered.

SQL> select next_day(sysdate,'tuesday') from dual;

NEXT_DAY(SYS
------------
24-MAR-09

SQL>

除此之外,还有注意的问题:

如果今天是星期二,如果要查询下一个 星期二,则为下周的星期二;如果要查询下一个星期三,要注意,返回值是这个周的星期三,强烈建议大家试一下,最能说明问题.从查询下一个周三开始，到下一个周六，返回的值都是这个周的周三到周六。

如果要把日期语言修改为简体中文命令如下,注意要把SIMPLIFIED CHINESE用单引号括起来,因为空格不属于合法的标识符.
alter SESSION set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE'

在实际应用是发现,next_day第二个参数使用数字也行,其1 代表周日,2代表周一....