Oracle日期格式处理

select TO_CHAR(TRUNC(add_months(SYSDATE, -9), ‘Q’), ‘yyyy’) || ‘.Q’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -9), ‘Q’), ‘Q’) time,
TRUNC(add_months(SYSDATE, -6), ‘Q’) -
TRUNC(add_months(SYSDATE, -9), ‘Q’) days
from dual
union all
select TO_CHAR(TRUNC(add_months(SYSDATE, -6), ‘Q’), ‘yyyy’) || ‘.Q’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -6), ‘Q’), ‘Q’) time,
TRUNC(add_months(SYSDATE, -3), ‘Q’) -
TRUNC(add_months(SYSDATE, -6), ‘Q’) days
from dual
union all
select TO_CHAR(TRUNC(add_months(SYSDATE, -3), ‘Q’), ‘yyyy’) || ‘.Q’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -3), ‘Q’), ‘Q’) time,
TRUNC(add_months(SYSDATE, 0), ‘Q’) -
TRUNC(add_months(SYSDATE, -3), ‘Q’) days
from dual
union all
select TO_CHAR(SYSDATE, ‘yyyy’) || ‘.Q’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, 0), ‘Q’), ‘Q’) time,
to_date(to_char(sysdate, ‘yyyy-mm-dd’), ‘yyyy-mm-dd’) -
TRUNC(add_months(SYSDATE, 0), ‘Q’) + 1 days
from dual
union all
select TO_CHAR(TRUNC(add_months(SYSDATE, -3), ‘MM’), ‘yyyy’) || ‘.M’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -3), ‘MM’), ‘MM’) time,
TRUNC(add_months(SYSDATE, -2), ‘MM’) -
TRUNC(add_months(SYSDATE, -3), ‘MM’) days
from dual
union all
select TO_CHAR(TRUNC(add_months(SYSDATE, -2), ‘MM’), ‘yyyy’) || ‘.M’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -2), ‘MM’), ‘MM’) time,
TRUNC(add_months(SYSDATE, -1), ‘MM’) -
TRUNC(add_months(SYSDATE, -2), ‘MM’) days
from dual
union all
select TO_CHAR(TRUNC(add_months(SYSDATE, -1), ‘MM’), ‘yyyy’) || ‘.M’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, -1), ‘MM’), ‘MM’) time,
TRUNC(add_months(SYSDATE, 0), ‘MM’) -
TRUNC(add_months(SYSDATE, -1), ‘MM’) days
from dual
union all
select TO_CHAR(SYSDATE, ‘yyyy’) || ‘.M’ ||
TO_CHAR(TRUNC(add_months(SYSDATE, 0), ‘MM’), ‘MM’) time,
to_date(to_char(sysdate, ‘yyyy-mm-dd’), ‘yyyy-mm-dd’) -
TRUNC(add_months(SYSDATE, 0), ‘MM’) + 1 days
from dual
union all
select to_char(sysdate - (rownum - 1) * 7, ‘yyyy’) || ‘.W’ ||
to_char(sysdate - (rownum - 1) * 7, ‘iw’) time,
case
when rownum = 1 then
to_date(to_char(sysdate, ‘yyyy-mm-dd’), ‘yyyy-mm-dd’) -
TRUNC(sysdate - (rownum - 1) * 7, ‘iw’) + 1
else
TRUNC(sysdate - (rownum - 1) * 7, ‘iw’) -
TRUNC(sysdate - (rownum) * 7, ‘iw’)
end days
from dual
connect by rownum <= 7