我们如何在今天提前两天获得，不包括周末在甲骨文？ [英] How do we get two days prior date from today excluding weekends In Oracle?
问题描述
我们怎样才能从今天的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.
推荐答案
这很简单。您需要今天的日期 [ ^ ]，然后loop [ ^ ]直到计数器小于3.在循环增加计数器的每一步中，如果周刊功能 [ ^ ]返回0到5.在循环的每个步骤中，您必须获取日期到变量（在循环中定义）使用 DateAdd函数 [ ^ ]（每次使用该变量并减去-1天）。
这就是全部！试试！
伪代码（我不熟悉Oracle）：
It's quite simple. You need to get today's date[^], then loop[^] till counter is less then 3. In each step of loop increase counter if weekday function[^] returns 0 to 5. In each step of loop you have to fetch date to variable (defined out of loop) using DateAdd function[^] (every time use that variable and substract -1 day).
That's all! Try!
In pseudo code (i'm not familiar with Oracle):
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 ;)
一种不同的方法，不需要PL / SQL块。
您可以使用递归查询来构建一组日期。在该查询中，检查星期几并基于此，将其包括在计算中。换句话说，只计算工作日。
请考虑以下声明：
A little bit different approach which doesn't require a PL/SQL block.
You can use a recursive query to build a set of dates. In that query check the day of the week and based on that, include it in calculation or not. In other words only working days are counted.
Consider the following statement:
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.
这篇关于我们如何在今天提前两天获得，不包括周末在甲骨文？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
