[Oracle] 透過 connect by 列出一個月內所有星期二的日期

Hits: 712

公司有個報表需求，需要列出這個月所有星期二的日期，發生的問題是我透過 DBeaver 可以正確的查出值，但是透過 python+cx_Oracle 反而無法解析。

列出一個月內所有星期二的日期

SELECT
   ROWNUM AS ID,
   TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'YYYYMMDD') AS MDATE,
   TO_CHAR(TRUNC(dt, 'MM') + LEVEL - 1, 'DY') AS M_DAY
FROM (SELECT TO_DATE(SYSDATE) dt FROM dual)
WHERE TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'DY') IN ('TUE')
CONNECT BY TRUNC(TRUNC(DT, 'MM') + LEVEL - 1, 'MM') = TRUNC(DT, 'MM');

DBeaver 的查詢結果

python+cx_Oracle 與其他 oracle client 都無法解析

同樣的指令，丟到 python 中就無法解析

換用 sql developer 試試看，也是不行

solution

與資深同事請教後發現，是 WHERE TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'DY') IN ('TUE') 這行的問題，改成 WHERE TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'D') IN ('3') 就正常了。

DBeaver

SQL Developer

python+cx_Oracle

可以發現 DBeaver 與 另外兩者在解析 TO_CHAR(TRUNC(dt, 'MM') + LEVEL - 1, 'DY') AS M_DAY 的時候有差異，DBeaver 會解析成 ‘TUE’ 而另外兩者解析為 ‘星期二’。

探究原因

去翻 TO_CHAT 的官方文件來看，得知 TO_CHAR 的語法可根據 nlsparam 這個參數來調整日期相關的顯示設定(如我範例的 TUE vs 星期二)

The ‘nlsparam’ argument specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
If you omit ‘nlsparam’, then this function uses the default date language for your session.

看起來就是 default date language 造成的差異，我們可以用 select * from nls_session_parameters 來驗證看看：

DBeaver

sql developer

python+cx_Oracle

從結果來看，的確是 NLS_LANGUAGE 的不同造成日期、時間等顯示上的不同，導致 filter 時因為可接受的參數不同所以無法回傳正確的查詢。

完整程式碼

SELECT 
   ROWNUM AS ID,
   TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'YYYYMMDD') AS MDATE,
   TO_CHAR(TRUNC(dt, 'MM') + LEVEL - 1, 'DY') AS M_DAY
FROM (SELECT TO_DATE(SYSDATE) dt FROM dual)
WHERE TO_CHAR(TRUNC(DT, 'MM') + LEVEL - 1, 'D') IN ('3')
CONNECT BY TRUNC(TRUNC(DT, 'MM') + LEVEL - 1, 'MM') = TRUNC(DT, 'MM');

TO_CHAR 可接受的 format 請參考此文件