-- 求一个给定日期距今天(当前)的工作日数! CREATE OR REPLACE FUNCTION workdays_today(i_date IN VARCHAR2) -- 输入日期的参数,用varchar2类型,其日期格式为:yyyy-mm-dd RETURN NUMBER IS v_day NUMBER(18,0); BEGIN SELECT COUNT(1) INTO v_day FROM ( SELECT to_date(i_date,'YYYY-MM-DD') + level - 1 as cdays FROM dual CONNECT BY level <= (SYSDATE - to_date(i_date,'YYYY-MM-DD') +1 )) T WHERE to_char(cdays,'D') <> 1 AND to_char(cdays,'D') <> 7; RETURN v_day; END; /SELECT workdays_today('2010-09-10') FROM DUAL;
-- 求两个日期之间的工作日数! -- 输入日期的参数,用varchar2类型,其日期格式为:yyyy-mm-dd CREATE OR REPLACE FUNCTION workdays_today( i_fromdate IN VARCHAR2, -- 起始日期 i_toDate IN VARCHAR2 -- 截止日期 ) RETURN NUMBER IS v_day NUMBER(18,0); v_fromdate DATE; v_todate DATE; BEGIN IF i_fromdate <= i_todate THEN v_fromdate := to_date(i_fromdate,'YYYY-MM-DD'); v_todate := to_date(i_todate,'YYYY-MM-DD'); ELSE v_fromdate := to_date(i_todate,'YYYY-MM-DD'); v_todate := to_date(i_fromdate,'YYYY-MM-DD'); END IF; SELECT COUNT(cdays)-1 INTO v_day FROM ( SELECT v_fromdate + level - 1 as cdays FROM dual CONNECT BY level <= (v_todate - v_fromdate + 1 ) ) T WHERE to_char(cdays,'D') <> 1 AND to_char(cdays,'D') <> 7; RETURN v_day; END; /SELECT workdays_today('2010-09-17','2010-09-17') FROM dual; SELECT workdays_today('2010-09-17','2010-09-18') FROM dual; SELECT workdays_today('2010-09-17','2010-09-19') FROM dual; SELECT workdays_today('2010-09-17','2010-09-20') FROM dual; SELECT workdays_today('2010-09-17','2010-09-21') FROM dual;
CREATE OR REPLACE FUNCTION workdays_today(i_date IN VARCHAR2) -- 输入日期的参数,用varchar2类型,其日期格式为:yyyy-mm-dd
RETURN NUMBER
IS
v_day NUMBER(18,0);
BEGIN
SELECT COUNT(1) INTO v_day FROM (
SELECT to_date(i_date,'YYYY-MM-DD') + level - 1 as cdays FROM dual
CONNECT BY level <=
(SYSDATE - to_date(i_date,'YYYY-MM-DD') +1 )) T
WHERE to_char(cdays,'D') <> 1
AND to_char(cdays,'D') <> 7;
RETURN v_day;
END;
/SELECT workdays_today('2010-09-10') FROM DUAL;
select * from tabname
where
(trunc(sysdate - REFUND_CREATE_TIME) - ((case
WHEN (8 - to_number(to_char(REFUND_CREATE_TIME, 'D'))) >
trunc(sysdate - REFUND_CREATE_TIME) + 1 THEN
0
ELSE
trunc((trunc(sysdate - REFUND_CREATE_TIME) -
(8 - to_number(to_char(REFUND_CREATE_TIME, 'D')))) / 7) + 1
END) + (case
WHEN mod(8 - to_char(REFUND_CREATE_TIME, 'D'), 7) >
trunc(sysdate - REFUND_CREATE_TIME) - 1 THEN
0
ELSE
trunc((trunc(sysdate - REFUND_CREATE_TIME) -
(mod(8 - to_char(REFUND_CREATE_TIME, 'D'), 7) + 1)) / 7) + 1
END))) >5
-- 输入日期的参数,用varchar2类型,其日期格式为:yyyy-mm-dd
CREATE OR REPLACE FUNCTION workdays_today(
i_fromdate IN VARCHAR2, -- 起始日期
i_toDate IN VARCHAR2 -- 截止日期
)
RETURN NUMBER
IS
v_day NUMBER(18,0);
v_fromdate DATE;
v_todate DATE;
BEGIN
IF i_fromdate <= i_todate THEN
v_fromdate := to_date(i_fromdate,'YYYY-MM-DD');
v_todate := to_date(i_todate,'YYYY-MM-DD');
ELSE
v_fromdate := to_date(i_todate,'YYYY-MM-DD');
v_todate := to_date(i_fromdate,'YYYY-MM-DD');
END IF; SELECT COUNT(cdays)-1 INTO v_day FROM (
SELECT v_fromdate + level - 1 as cdays FROM dual
CONNECT BY level <=
(v_todate - v_fromdate + 1 ) ) T
WHERE to_char(cdays,'D') <> 1
AND to_char(cdays,'D') <> 7; RETURN v_day;
END;
/SELECT workdays_today('2010-09-17','2010-09-17') FROM dual;
SELECT workdays_today('2010-09-17','2010-09-18') FROM dual;
SELECT workdays_today('2010-09-17','2010-09-19') FROM dual;
SELECT workdays_today('2010-09-17','2010-09-20') FROM dual;
SELECT workdays_today('2010-09-17','2010-09-21') FROM dual;