sql oracle sysdate:忽略周末

Question

我在我的where中使用此代码：

and table.datum >= sysdate-7
and table.datum <= sysdate

它显示了过去7天的数据--我想补充的是，它忽略了周末(周六和周日)--我检查了互联网，但没有什么对我有用！

Answer 1

在其中使用并添加以下where语句。

AND table.datum BETWEEN sysdate-7 and sysdate
AND TO_CHAR(to_date(substr(table.datum,1,10), 'DD-MM-YYYY'),
                         'D', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('1', '7')

Answer 2

“马特”的回答不是错误证明。TO_CHAR(..., 'D'的结果取决于NLS_TERRITORY，而NLS_TERRITORY在每个会话上都可能有所不同。由于TO似乎是德语(使用DATUM作为日期表达式)，我甚至感到惊讶的是，马特的解决方案在他的机器上工作。

参见此测试用例：

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

WITH d AS 
    (SELECT DATE '2015-03-01' + LEVEL AS datum FROM dual CONNECT BY LEVEL <= 31)
SELECT TO_CHAR(datum, 'yyyy-mm-dd fmDay')
FROM d
WHERE TO_CHAR(datum, 'D', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('1', '7')
    AND datum BETWEEN SYSDATE-7 AND SYSDATE;

TO_CHAR(DATUM,'YYYY-MM-DDFMDAY')
2015-03-18 Wednesday
2015-03-19 Thursday
2015-03-20 Friday
2015-03-23 Monday
2015-03-24 Tuesday


ALTER SESSION SET NLS_TERRITORY = 'GERMANY';

WITH d AS 
    (SELECT DATE '2015-03-01' + LEVEL AS datum FROM dual CONNECT BY LEVEL <= 31)
SELECT TO_CHAR(datum, 'yyyy-mm-dd fmDay')
FROM d
WHERE TO_CHAR(datum, 'D', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('1', '7')
    AND datum BETWEEN SYSDATE-7 AND SYSDATE;

TO_CHAR(DATUM,'YYYY-MM-DDFMDAY')
2015-03-18 Wednesday
2015-03-19 Thursday
2015-03-20 Friday
2015-03-21 Saturday
2015-03-24 Tuesday

更安全的方法是使用Dy或fmDay格式

WITH d AS 
    (SELECT DATE '2015-03-01' + LEVEL AS datum FROM dual CONNECT BY LEVEL <= 31)
SELECT TO_CHAR(datum, 'yyyy-mm-dd fmDay')
FROM d
WHERE TO_CHAR(datum, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('Sun', 'Mon')
    AND datum BETWEEN SYSDATE-7 AND SYSDATE;