最近在做同比环比报表,2月份的日期需要判断是否闰年,在网上查了很多资料,大部分都是用sysdate说明的,不是很理想,自己捣鼓了半天,终于整出来了。
跟大家分享下。
declare
vbdate date;
V_LASTDAY NUMBER(2,0);
V_MONTH NUMBER(2,0);
V_DAY DATE;
begin
vbdate := to_date('2016-02-28','yyyy.mm.dd');
SELECT TO_NUMBER(SUBSTR(VBDATE,9,2)) INTO V_LASTDAY FROM DUAL;
SELECT TO_NUMBER(SUBSTR(VBDATE,6,2)) INTO V_MONTH FROM DUAL;
delete hd_report.hdtmp_dxd_phmx;
commit;
--------------
IF V_LASTDAY=29 AND V_MONTH=2
THEN
select to_date(to_char(vbdate+INTERVAL '-1' DAY,'yyyy.mm')||'-28 23:59;59','yyyy.mm.dd hh24:mi:ss') INTO V_DAY FROM DUAL;
INSERT INTO hd_report.hdtmp_dxd_phmx
(num,cls,gdcode,fildate)
SELECT '-','-',0,to_date(to_char(V_DAY+INTERVAL '-1' year,'yyyy.mm.dd')||'23:59;59','yyyy.mm.dd hh24:mi:ss')
from dual;
ELSE
insert into hd_report.hdtmp_dxd_phmx
(num,cls,gdcode,fildate)
select '-','-',0,to_date(to_char(last_day(vbdate+INTERVAL '-1' year),'yyyy.mm.dd')||'23:59;59','yyyy.mm.dd hh24:mi:ss')
from dual;
END IF;
end;
查询结果:
select fildate from hd_report.hdtmp_dxd_phmx;
1 2015-02-28 23:59:59