addweek oracle_相当于DATEADD（工作日，-3，GETDATE（））的Oracle（10g）

I'm looking for the Oracle (10g) equivalent of:

DATEADD(weekday, -3, GETDATE())

from T-SQL (SQL Server) . This subtracts 3 weekdays from 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.

解决方案

It looks like you need to create a 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.