隔月分组查询:
create table t3 (c1 varchar(22),c2 number(10,2));
insert into t3 values ('2012-02-01',100.55);
insert into t3 values ('2012-03-11',200);
insert into t3 values ('2012-03-21',333);
insert into t3 values ('2012-04-01',111);
insert into t3 values ('2012-04-20',555);
insert into t3 values ('2012-04-22',321);
insert into t3 values ('2012-05-01',222.22);
insert into t3 values ('2012-05-29',155.15);
insert into t3 values ('2012-06-11',234);
commit;
查询上月21到本月20号,思想将上月的数据也归并到本月
select substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7) ,sum(c2) c2
from t3
group by substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7)
order by substr(to_char(add_months((to_date(c1,'yyyy-mm-dd')-20),1),'yyyy-mm-dd'),1,7)
查询结果分支语句:
select (case
when to_date('2008-05-01', 'yyyy-MM-dd') >=
to_date('2008-03-01', 'yyyy-MM-dd') then
to_date('2008-05-01', 'yyyy-MM-dd')
else
to_date('2008-03-01', 'yyyy-MM-dd')
end) test
from dual
月份去零:
select ltrim(to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'mm'),'0') from dual;
select to_number(to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'mm')) from dual;
select to_char(to_date('2008-01-01', 'yyyy-MM-dd'),'fmmm') from dual;
日期之间天数:
select trunc(sysdate)-trunc(TO_DATE('2019-08-24','YYYY-MM-DD')) from dual;
网友评论