windows - Oracle 无法处理日语操作系统问题

Question

我有一个查询返回给定年份的第二个和第四个星期六，

WITH ALL_SATURDAYS AS
  (SELECT TO_CHAR(TO_DATE('01012014','DDMMYYYY'),'WW')                              * (level)                                                                                       AS WEEK_NO,
    NEXT_DAY(TO_DATE('01012014','DDMMYYYY')                                         + (TO_CHAR(TO_DATE('01012014','DDMMYYYY'),'WW' ) * (level-1) * 7),'土')                        AS SATURDAY_DATE,
    row_number() OVER (PARTITION BY TO_CHAR(NEXT_DAY(TO_DATE('01012014','DDMMYYYY') + (TO_CHAR(TO_DATE('01012014','DDMMYYYY'),'WW' ) * (level-1) * 7),'土'),'月') ORDER BY level) AS Pos
  FROM DUAL
    CONNECT BY level<= 52
  ORDER BY 1
  )
SELECT SATURDAY_DATE,POS FROM ALL_SATURDAYS WHERE POS IN (2,4) ORDER BY 1,2

它在其他系统中工作，但我的是日文操作系统，当我执行此查询时它返回 ORA-01821: date format not recognized 错误。

如何修复错误？

Answer 1

将月替换为MONTH。日期时间格式元素始终使用英语，即使结果可能使用另一种语言。

alter session set nls_date_language=japanese;
select to_char(date '2014-01-01', 'MONTH') month from dual;

Month
-----
1月