oracle的next_day

關鍵字: oracle日期函數 next_day

在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代表周一....