oracle 时间比较sql

--获取到上个月今天
SELECT add_months(sysdate,-1) as from dual;
SELECT trunc(add_months(sysdate,-1)) as from dual;
--获取到去年今天
SELECT add_months(sysdate,-12) as from dual;
SELECT trunc(add_months(sysdate,-12)) as from dual;
--获取到半年前
SELECT add_months(sysdate,-6) as from dual;
SELECT trunc(add_months(sysdate,-6)) as from dual;
--获取昨天
select sysdate-1  from dual;
select trunc(sysdate-1)  from dual;
--获取到一周
select sysdate-7  from dual;
select trunc(sysdate-7)  from dual;

--比较
select sysdate, to_date('2015-03-02 22:12:10','yyyy-mm-dd hh24:mi:ss') from dual where sysdate<to_date('2015-03-02 22:12:10','yyyy-mm-dd hh24:mi:ss')
--一天之内
select sysdate from dual where to_date('2015-03-01 22:12:10','yyyy-mm-dd hh24:mi:ss')>=(sysdate-1)
--一周之内
select sysdate from dual where to_date('2015-03-01 22:12:10','yyyy-mm-dd hh24:mi:ss')>=(sysdate-7)
--一个月以内
select sysdate from dual where to_date('2015-02-21 22:12:10','yyyy-mm-dd hh24:mi:ss')>=add_months(sysdate,-1)
--半年
select sysdate from dual where to_date('2015-02-21 22:12:10','yyyy-mm-dd hh24:mi:ss')>=add_months(sysdate,-6)
--一年
select sysdate from dual where to_date('2015-02-21 22:12:10','yyyy-mm-dd hh24:mi:ss')>=add_months(sysdate,-12)
--一年以上
select sysdate from dual where to_date('2015-02-21 22:12:10','yyyy-mm-dd hh24:mi:ss')<add_months(sysdate,-12)