本文主要是介绍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;
这篇关于oracle查询当前日期的当月日历表的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!