我们如何在今天提前两天获得，不包括周末在甲骨文？

问题描述

我们怎样才能从今天的Oracle日期开始提前两天？



比方说，今天的日期是2015年8月13日，所以结果应该是8/10/2015



但是在周末的情况下，

假设今天的日期是2015年8月8日，所以结果应该是8/05/2015





请指教。



谢谢。

Hi,

How can we get two days prior date from today's date in Oracle?

lets say, today's date is 8/13/2015 so the result should be 8/10/2015

But in the case of weekends,
lets say today's date is 8/10/2015 so the result should be 8/05/2015


Please advise.

Thanks.

推荐答案

DECLARE:
var @counter INT :=1
var @twoWorkingDaysBefore DATETIME := SYSDATE()
BEGIN
    WHILE counter<3
        @twoWorkingDaysBefore := DATEADD(@twoWorkingDaysBefore INTERVAL -1 DAY)
        IF(WEEKDAY(@twoWorkingDaysBefore)>=0 AND  WEEKDAY(@twoWorkingDaysBefore) <5) 
        BEGIN
            @counter := @counter +1
        END
    LOOP
END
-- @twoWorkingDaysBefore stores proper date ;)

WITH dates (SingleDate, DayOfWeek, Include, DayCumulator) AS (
   SELECT TRUNC(SYSDATE - level + 1) AS SingleDate,
          TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D')) AS DayOfWeek,
          CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END AS Include,
          SUM(CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END ) OVER (ORDER BY TRUNC(SYSDATE - level + 1) DESC) AS DayCumulator
   FROM dual
   CONNECT BY Level <= 1000
)
SELECT SingleDate,
       DayOfWeek,
       Include,
       DayCumulator
FROM dates

它将返回结果集喜欢

It would return a result set like

SingleDate  DayOfWeek  Include   DayCumulator
----------  ---------  -------   ------------  
15.08.2015  6          1         1
14.08.2015  5          1         2
13.08.2015  4          1         3
12.08.2015  3          1         4
11.08.2015  2          1         5
10.08.2015  1          0         5
09.08.2015  7          0         5
08.08.2015  6          1         6
07.08.2015  5          1         7
06.08.2015  4          1         8
05.08.2015  3          1         9
04.08.2015  2          1         10
03.08.2015  1          0         10
02.08.2015  7          0         10
01.08.2015  6          1         11
...

所以现在要在两者之间获得一定天数的日期，只需添加WHERE条件，例如

So now to get the date with certain amount of days in between, just add a WHERE condition, like

WITH dates (SingleDate, DayOfWeek, Include, DayCumulator) AS (
   SELECT TRUNC(SYSDATE - level + 1) AS SingleDate,
          TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D')) AS DayOfWeek,
          CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END AS Include,
          SUM(CASE TO_NUMBER(TO_CHAR(SYSDATE - level + 1, 'D'))
             WHEN 1 THEN 0
             WHEN 7 THEN 0
             ELSE 1
          END ) OVER (ORDER BY TRUNC(SYSDATE - level + 1) DESC) AS DayCumulator
   FROM dual
   CONNECT BY Level <= 1000
)
SELECT SingleDate,
       DayOfWeek,
       Include,
       DayCumulator
FROM dates
WHERE DayCumulator = 7;

结果将是

The result would be

SingleDate  DayOfWeek  Include   DayCumulator
----------  ---------  -------   ------------  
07.08.2015  5          1         7

请注意，NLS设置可能会影响每个工作日返回的数字。

Note that NLS settings may affect which number is returned for each weekday.

这篇关于我们如何在今天提前两天获得，不包括周末在甲骨文？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！