需求是要在sql语句里查询出要求的订单,这个订单有一个字段是退款创建时间REFUND_CREATE_TIME(Date类型),用sql语句求出系统当前时间SYSTEM_TIME与REFUND_CREATE_TIME间隔是否超过5个工作日(不包括周六和周末)
希望大神们 能详细的写出这个sql语句 拯救小弟于危难之中
希望大神们 能详细的写出这个sql语句 拯救小弟于危难之中
解决方案 »
- 同一个事务 报“未找到父项关键字” 急
- oracle产品限制50用户,这限制50用户从技术角度来说是什么意思?
- 如何取得指定年月的最后一条记录?
- vc 2005中怎样配置Oralce OCI
- 急急急oracle数据转成dbf格式文件
- Oracle 中,同一个表空间,不同用户对表中的数据操作会互相影响吗?
- 刚装的oracle10g 监听器启不了!帮帮我.
- 百分:有什么办法可以直接写SP或SF来设置SEQUENCE的NEXTVAL?
- oracle中自带的sys和system两个用户有什么区别?
- 那位高手用过Oracle的Advanced Queuing,能举一个例子吗?,用Q可以传输数据吗?(有分)
- oracle中的sql转成java代码,有工具否,标题要长点
- IMP-00017: following statement failed with ORACLE error 2248:
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;