ORA-01846错误解决办法

张兴康

select order#,orderdate,next_day(orderdate,'MONDAY') "Should Shipdate"
from orders
where order# = 1010;
执行上面的语句发现错误。

ORA-01846: 周中的日无效
是oracle中关于时间拼写的参数（nls_date_language）设置有问题，改成下面的格式就没有错了，用数值1~7分别表示星期天道星期六。一周的第一天是从周天开始的。
SQL> select order#,orderdate,next_day(orderdate,2) "Should Shipdate"
  2  from orders
  3  where order# = 1010;

ORDER# ORDERDATE   Should Shipdate
------ ----------- ---------------
  1010 2009/4/3    2009/4/6
也可以修改参数然后在执行语句
SQL> alter session set nls_date_language='american';
Session altered

SQL> select order#,orderdate,next_day(orderdate,'MONDAY') "Should Shipdate"
  2  from orders
  3  where order# = 1010;

ORDER# ORDERDATE   Should Shipdate
------ ----------- ---------------
  1010 2009/4/3    2009/4/6
参考：
NLS_DATE_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
NLS_LANGUAGE specifies the default language of the database. This language is used
for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default
sorting mechanism. This parameter also determines the default values of the parameters
NLS_DATE_LANGUAGE and NLS_SORT.

张兴康