Question

oracle怎么计算两个日期之间去除周末的天数？

我有一张表有个日期字段，现在想计算当前系统时间减去该日期字段所得的工作天数，去除周六周日的，该怎么写？不要存储过程的，先谢谢各位~~

Answer 1

CREATE OR REPLACE function
num_Business_Days(start_date IN date, end_date IN date)
RETURN number IS
currdate date := start_date; /* holds the next date */
theDay varchar2(10); /* day of the week for currdate */
countBusiness number := 0; /* counter for business days */

BEGIN

/* start date must be earlier than end date */
IF end_date - start_date <= 0 THEN
RETURN (0);
END IF;

LOOP
/* go to the next day */
currdate := TO_DATE(currdate+1);

/* finished if end_date is reached */
EXIT WHEN currdate = end_date;

/* what day of the week is it? */
SELECT TO_CHAR(currdate,'fmDay') INTO theDay FROM dual;

/* count it only if it is a weekday */
IF theDay <> 'Saturday' AND theDay <> 'Sunday' THEN
countBusiness := countBusiness + 1;
END IF;

END LOOP;

RETURN (countBusiness);

END;

Answer 2

没环境,写下思路:
select floor((D1 - D2 +1)/7) * 2 +
case when D2=Sun and D1=Sun 则1天
D2=Sun and D1<=Sat and D1>=Tue 则2天
D2=Sat and D1>=Mon 则1天 from dual;
没环境, 谁能借我一个远程的环境