解决next_day（当前日期下星期星期几对应日期）报错原因

解决next_day（当前日期下星期星期几对应日期）报错原因
select next_day (sysdate,'TUESDAY') NEXT_DAY FROM DUAL;
ERROR 位于第 1 行:
ORA-01846: 周中的日无效
select next_day (sysdate,'星期二') NEXT_DAY FROM DUAL;
NEXT_DAY                 
-------------------------
27-7月-10             

修改nls_date_language
alter session set nls_date_language='American';
select next_day (sysdate,'TUESDAY') NEXT_DAY FROM DUAL;
NEXT_DAY                 
-------------------------
27-7月-10

修改nls_date_language
alter session set nls_date_language='simplified chinese';
select next_day (sysdate,'星期二') NEXT_DAY FROM DUAL;
NEXT_DAY                 
-------------------------
27-7月-10

还可以使用next_day (sysdate,n),和参数nls_date_language就没关系了
select next_day (sysdate,3) NEXT_DAY FROM DUAL;
NEXT_DAY                 
-------------------------
27-7月-10

查询中直接使用NLS_DATE_LANGUAGE参数
select to_char(sysdate,'Day','NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=AMERICAN')
---------------------------------------------------
Tuesday
select to_char(sysdate,'DD-MON-YY','NLS_DATE_LANGUAGE = American') vdate from dual;
VDATE       
------------
20-JUL-10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21126685/viewspace-668421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21126685/viewspace-668421/