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

點閱: 45

公司有個報表需求,需要列出這個月所有星期二的日期,發生的問題是我透過 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 的查詢結果
file

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

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

file

換用 sql developer 試試看,也是不行
file

solution

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

DBeaver
file

SQL Developer
file

python+cx_Oracle
file

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

探究原因

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

file

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
file

sql developer
file

python+cx_Oracle
file

從結果來看,的確是 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 請參考此文件

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts