Oracle (10g) 相当于 DATEADD(weekday, -3, GETDATE())
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5670194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle (10g) equivalent of DATEADD(weekday, -3, GETDATE())
提问by Cade Roux
I'm looking for the Oracle (10g) equivalent of:
我正在寻找相当于以下内容的 Oracle (10g)：
DATEADD(weekday, -3, GETDATE())
from T-SQL (SQL Server) . This subtracts 3 weekdaysfrom the current date. I'm not concerned about holidays or anything like that (and I can truncate the time part off myself). Just excluding weekends is fine.
来自 T-SQL (SQL Server)。这将从当前日期中减去 3 个工作日。我不关心假期或类似的事情（我可以自己截断时间部分）。不包括周末就可以了。
回答by Martin Schapendonk
It can be done without a PL/SQL function. Just subtract a different number of days depending on the day of the week:
它可以在没有 PL/SQL 函数的情况下完成。只需根据星期几减去不同的天数：
select trunc(sysdate) - case to_char(sysdate, 'D')
when '4' then 3 -- thursday minus 3 days
when '5' then 3 -- friday minus 3 days
when '6' then 4 -- saturday minus 4 days
else 5 -- all other days minus 5 days
end
from dual;
When you have to do this for e.g. 12 days back, it would look like:
当您必须在例如 12 天前执行此操作时，它看起来像：
select trunc(sysdate) - case to_char(sysdate, 'D')
when '1' then 18 -- mondays minus 18 days (incl. 3 weekends)
when '2' then 18 -- tuesdays minus 18 days (incl. 3 weekends)
when '6' then 17 -- saturdays minus 17 days (incl. 2 weekends and a saturday)
else 16 -- all others minus 16 days (incl. 2 weekends)
end
from dual;
Please note that day of week depends on the NLS_TERRITORY of your database (in America day 1 is Sunday, in most others day 1 is Monday).
请注意，星期几取决于您数据库的 NLS_TERRITORY（在美国，第 1 天是星期日，在大多数其他国家，第 1 天是星期一）。
回答by Forgotten Semicolon
It looks like you need to create a UDF.
看起来您需要创建一个 UDF。
CREATE OR REPLACE FUNCTION business_date (start_date DATE,
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;
WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
DayNum := TO_CHAR( CurDate, 'D');
IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
Courtesy of Larry Benton, from here.
由拉里·本顿 (Larry Benton) 提供，来自此处。
