//FUNCTION 创建,此函数自动去除周六日:
CREATE OR REPLACE
FUNCTION calcDates(START_DATE IN DATE, END_DATE IN DATE,HOLIDAY_LIST IN VARCHAR2)RETURN number IS
No_of_DAYS number;
BEGIN
IF START_DATE < END_DATE THEN
SELECT count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(START_DATE) + level - 1 dayList
FROM dual
connect BY trunc(START_DATE) + level - 1 < = trunc(END_DATE)) A
where not exists (select 1
from (SELECT COLUMN_VALUE AS ph_date FROM TABLE (fn_split(HOLIDAY_LIST,','))) b
where to_date(b.ph_date, 'YYYY-MM-DD') = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
ELSE
SELECT 0 - count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(END_DATE) + level - 1 dayList
FROM dual
connect BY trunc(END_DATE) + level - 1 < = trunc(START_DATE)) A
where not exists (select 1
from (SELECT COLUMN_VALUE AS ph_date FROM TABLE (fn_split(HOLIDAY_LIST,','))) b
where to_date(b.ph_date, 'YYYY-MM-DD') = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
END IF;
Return No_of_DAYS;
END;
//查询示例:
SELECT calcDates(TO_DATE('2015-04-01', 'yyyy-MM-dd'),TO_DATE('2015-04-08', 'yyyy-MM-dd'),'2015-04-08,2015-04-05') FROM dual;
//查询结果:
//2015年4月1日到2015年4月8日一共8天,去除两个周六日4号和5号,再去除参数中特定的8号,结果为5天。
参数:START_DATE 为开始时间。
END_DATE 为结束时间。
HOLIDAY_LIST 为需要剔除的假日,格式为: '2015-01-01,2015-02-01,2015-03-04' 。
代码解析:
1、 to_char(dayList, 'D') not in (1, 7);
to_char(dayList, 'D')返回的是 日期是星期几,类似于java里面的 Calendar.DAY_OF_WEEK,
not in (1, 7)就是不在周六或者周日, 1是周日, 7是周六 ,这个和java一样。
2、 trunc(START_DATE) trunc 函数拿到的是日期的值,忽略小时数。
3、SELECT DISTINCT trunc(START_DATE) + level - 1 dayList
FROM dual
connect BY trunc(START_DATE) + level - 1 < = trunc(END_DATE)
这段代码就是列出两个日期之间所有日期。
注:fn_split 为 类似于java中的split函数,此函数为自己定义,详见:http://blog.csdn.net/pingyan158/article/details/17923181
此文参照地址:http://blog.sina.com.cn/s/blog_6b6ba82d0100lxcv.html