– 1.日
–环比
–获取昨天
select to_char(TRUNC(to_date(‘20211223’,‘yyyymmdd’)-1),‘yyyymmdd’) from dual;
–同比
–获取去年的今日
select to_char(add_months(trunc(to_date(‘20211223’,‘yyyymmdd’)),-12),‘yyyymmdd’) from dual;
– 2.月
–环比
–获取当月第一天
select to_char(trunc(to_date(‘20211223’,‘yymmdd’), ‘mm’),‘yyyymmdd’) monthFirstDay from dual;
–获取上个月第一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yymmdd’), ‘mm’),-1),‘yyyymmdd’) monthFirstDay from dual;
–获取上个月最后一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yymmdd’), ‘mm’),-0)-1,‘yyyymmdd’) monthFirstDay from dual;
–同比
–获取去年这个月第一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yymmdd’), ‘mm’),-12),‘yyyymmdd’) monthFirstDay from dual;
–获取去年这个月最后一天
select to_char(trunc(last_day(add_months(trunc(to_date(‘20211223’,‘yymmdd’), ‘mm’),-12))),‘yyyymmdd’) from dual;
– 3. 季度
– 环比
–获取上个季度第一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yyyymmdd’), ‘q’), -3), ‘yyyymmdd’) from dual;
–获取上个季度最后一天
select to_char(trunc(to_date(‘20211223’,‘yyyymmdd’), ‘q’) - 1, ‘yyyymmdd’) from dual;
– 获取本季度的第一天
select to_char(trunc(to_date(‘20211223’,‘yyyymmdd’),‘q’),‘yyyymmdd’) from dual;
–同比
– 去年当前季度的第一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yyyymmdd’),‘q’),-12),‘yyyymmdd’) from dual;
– 去年本季度的最后一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yyyymmdd’),‘q’)-1,-9),‘yyyymmdd’) from dual;
–4.年
–同比
– 当年第一天
select to_char(trunc(to_date(‘20211223’,‘yyyymmdd’),‘yyyy’),‘yyyymmdd’) from dual;
–去年第一天
select to_char(add_months(trunc(to_date(‘20211223’,‘yyyymmdd’),‘yyyy’),-12),‘yyyymmdd’) from dual;
–去年最后一天
select to_char(trunc(to_date(‘20211223’,‘yyyymmdd’),‘yyyy’)-1,‘yyyymmdd’) from dual;
– 指定日期减一年:
select to_char(add_months(to_date(‘20220102’, ‘yyyymmdd’), -12), ‘yyyymmdd’) from dual;
– 指定日期减一月:
select to_char(add_months(to_date(‘20220102’, ‘yyyymmdd’), -1), ‘yyyymmdd’) from dual;