oracle计算当前的上月、上一周的时间

--上一月

select to_char(trunc(sysdate,'MM')-1,'yyyy-MM-dd') from dual;

--建立函数上周的开始时间

create or replace function fun_acc_getlastweekstart(systemdate in date)
  return varchar2 is
  result_str varchar2(15);
begin
  select to_char(trunc(systemdate, 'iw') - 7, 'yyyymmdd')
    into result_str
    from dual;
  return result_str;
end fun_acc_getlastweekstart;

 

--建立函数上周的结束时间

create or replace function fun_acc_getlastweekend(systemdate in date) return varchar2 is
  result_str varchar2(15);
begin
  select to_char(trunc(systemdate, 'iw') - 1, 'yyyymmdd')
    into result_str
    from dual;
  return result_str;
end fun_acc_getlastweekend;

--查询

select  to_date(fun_acc_getlastweekstart(sysdate),'yyyy-MM-dd') from dual;  
select to_date(fun_acc_getlastweekend(sysdate),'yyyy-MM-dd') from dual;  

select count(1) from dual 
where to_date('2018/10/23','yyyy/MM/dd') between to_date(fun_acc_getlastweekstart(sysdate),'yyyy/MM/dd') and  to_date(fun_acc_getlastweekend(sysdate),'yyyy/MM/dd')

select count(1) from dual 
where to_date('2018/10/20','yyyy/MM/dd') between to_date(fun_acc_getlastweekstart(sysdate),'yyyy/MM/dd') and  to_date(fun_acc_getlastweekend(sysdate),'yyyy/MM/dd')