甲骨文不处理日本操作系统问题

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。Datetime格式元素总是以英语表示，即使结果可能是另一种语言。

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

Month
-----
1月