select * from table t,(SELECT systimestamp-NUMTODSINTERVAL(60,'day') as time FROM DUAL) a
where t.result<>'SUCCESS' and t.create<a.time
select * from table t,(SELECT systimestamp-NUMTODSINTERVAL(60,'day') as time FROM DUAL) a
where t.create>a.time
create table test as select * from table
SELECT systimestamp-NUMTODSINTERVAL(60,'day')from dual
delete from table t where t.result='SUCCESS' and t.*<((SELECT systimestamp-NUMTODSINTERVAL(10,'day')from dual))
numtodsinterval(<x>,<c>) ,x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 ('day','hour','minute','second')
numtoyminterval 与numtodsinterval函数类似,将x转为interval year to month数据类型
常用的单位有'year','month'
NumToYMInterval(1, 'YEAR') :一年后的间隔
NumToYMInterval(-1, 'MONTH'): 一个月前
小数会被计算成整数后,再做计算:
select sysdate + numtoyminterval(0.1, 'MONTH') as future from dual;\
FUTURE
------------------
11-OCT-13
该函数的结果是:”INTERVAL YEAR TO MONTH literal“。不能与数值做运算。
select 1 + NumToYMInterval(1, 'MONTH') from dual
Oracle会返回一个错误。
做日期运算时,这个函数非常有用。例如:取一个月后的日期:
select sysdate + NumToYMInterval(1, 'MONTH') from dual;