oracle查询当前日期的当月日历表

根据给定时间，查询 当月的日历表。

WITH v1 AS
(SELECT sysdate v_date FROM DUAL),
    v2 AS
(SELECT TRUNC(v_date,'mm') begin_month,add_months(TRUNC(v_date,'mm'),1) next_month FROM v1 ),
   v3 AS
(SELECT  begin_month+(level-1) AS d FROM v2 CONNECT BY level<=(next_month-begin_month)),
   v4 AS 
(SELECT TRUNC(d,'day') order_day,to_char(d,'dd') v_day,to_number(to_char(d,'d')) week_day FROM v3 )
SELECT MAX(decode(week_day,1,v_day)) "日",
       MAX(decode(week_day,2,v_day)) "一",
       MAX(decode(week_day,3,v_day)) "二",
       MAX(decode(week_day,4,v_day)) "三",
       MAX(decode(week_day,5,v_day)) "四",
       MAX(decode(week_day,6,v_day)) "五",
       MAX(decode(week_day,7,v_day)) "六"
      
FROM v4
GROUP BY order_day
ORDER BY order_day;

效果：

全年日历：

WITH v1 AS
(SELECT sysdate v_date FROM DUAL),
    v2 AS
(SELECT TRUNC(v_date,'mm') begin_year,add_months(TRUNC(v_date,'mm'),12) next_year FROM v1 ),
   v3 AS
(SELECT  begin_year+(level-1) AS d FROM v2 CONNECT BY level<=(next_year-begin_year)),
   v4 AS 
(SELECT TRUNC(d,'day') order_day,to_char(d,'mm') v_month,to_char(d,'dd') v_day,to_number(to_char(d,'d')) week_day FROM v3 )
SELECT DECODE(ROW_NUMBER() OVER(partition by v_month order by order_day),1, v_month) "月份",
       MAX(decode(week_day,1,v_day)) "日",
       MAX(decode(week_day,2,v_day)) "一",
       MAX(decode(week_day,3,v_day)) "二",
       MAX(decode(week_day,4,v_day)) "三",
       MAX(decode(week_day,5,v_day)) "四",
       MAX(decode(week_day,6,v_day)) "五",
       MAX(decode(week_day,7,v_day)) "六"
      
FROM v4
GROUP BY  v_month, order_day
ORDER BY  v_month, order_day;