SELECT to_char(sysdate,'iw') from dual; --本周是第几个自然周
SELECT to_char(sysdate,'yyyy') into v_sbzq_nf from dual; -- 当前年份
SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),'IW'),'yyyy-MM-dd') FROM DUAL;--本周的起始时间(本周周一日期)
SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'YYYY-MM-DD'),'IW') + 6,'yyyy-MM-dd') FROM DUAL;--本周的结束时间(本周周日日期)
下面是使用存储过程向数据库中插入一条数据
数据格式 : 上报周期 起始时间 结束时间
xxxx年第xx周 xx.xx xx.xx
存储过程如下:
create or replace procedure PRC_T_SJSB_ZYGYPJG is
v_sbzq_zs varchar2(20);--上报周期周数
v_sbzq_nf varchar2(10); --上报周期年份
v_start varchar2(10); --起始月份
v_end varchar2(10); --结束月份
v_sbzq varchar2(20); --上报周期
begin
SELECT to_char(sysdate,'iw') into v_sbzq_zs from dual; --本周是第几个自然周
SELECT to_char(sysdate,'yyyy') into v_sbzq_nf from dual;
SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),'IW'),'yyyy-MM-dd') into v_start FROM DUAL;--本周的起始时间(本周周一日期)
SELECT to_char(TRUNC(TO_DATE(to_char(sysdate,'yyyy-MM-dd'),'YYYY-MM-DD'),'IW') + 6,'yyyy-MM-dd') into v_end FROM DUAL;--本周的结束时间(本周周日日期)
v_start:=substr(v_start,6);
v_end :=substr(v_end,6);
v_start :=replace(v_start,'/','.');
v_start :=replace(v_start,'-','.');
v_end :=replace(v_end, '/','.');
v_end :=replace(v_end, '-','.');
v_sbzq :=v_sbzq_nf||'年第'||v_sbzq_zs||'周';
insert into T_SJSB_ZYGYPJG (sbzq,sbzt,startdate,enddate) values(v_sbzq,'0',v_start,v_end);
commit;
end PRC_T_SJSB_ZYGYPJG;
select to_char(created_time,'yyyymmdd') as day,count(mobile_no)
from table
where to_char(created_time,'yyyymmdd') >= 20181201
group by to_char(created_time,'yyyymmdd')
order by min(created_time) asc
select to_char(created_time,'iw') as week, min(created_time), count(mobile_no)
from table
where to_char(created_time,'yyyymmdd') >= 20181201
group by to_char(created_time,'iw')
order by min(created_time) asc
select to_char(created_time,'yyyymm') as Month, count(mobile_no)
from table
where to_char(created_time,'yyyymm') >= 20181201
group by to_char(created_time,'yyyymm')
其结果如下所示:
4. 按季统计
to_char()的格式参数值为’q’,可实现按季度输出统计结果。
select to_char(created_time,'q') as q,count(mobile_no)
from table
where to_char(created_time,'yyyymmdd') >= 20180101
group by to_char(created_time,'q')
order by min(created_time) asc
其结果如下所示,Q表示季度。
5. 按年统计
to_char()函数的参数值为’yyyy’时可以实现按年输出统计结果。
select to_char(created_time,'yyyy') as y, count(mobile_no)
from table
where to_char(created_time,'yyyy') >= 2016
group by to_char(created_time,'yyyy')
order by y asc