ORACLE获取当天所在月份的所有日期和周几(每月类似台历显示)

台历显示)"/>

ORACLE获取当天所在月份的所有日期和周几(每月类似台历显示)

背景： 做看板的时候，其中有一项内容是，需要根据在看板上显示当月的周别，即类似台历的页面，以大屏的形式显示。
当时然后想了好久，突然间想到好久之前，帮忙写过一个SQL，需要显示当年的所有周别，同时需要显示周一和周日的日期，如下链接：

然后我想了下，那个SQL是取周一和周日的，那我稍微调整下，就可以把周二到周六也抓出来了，不多说，上SQL：

--获取当天所在月份的所有日期和周几(类似台历)
select to_char(sysdate,'yyyy/MM/DD') NOW_DATE,--当天日期to_char(monday,'MM') monday_month,to_char(sunday,'MM') sunday_month,week,--周别to_char(monday,'DD') monday,--周一号to_char(monday,'YYYY-MM-DD') AS MondayWhole,--周一日期to_char(monday+1,'DD') Tuesday,--周二号to_char(monday+1,'YYYY-MM-DD') AS TuesdayWhole,--周二日期to_char(monday+2,'DD') Wednesday,--周三号to_char(monday+2,'YYYY-MM-DD') AS WednesdayWhole,--周三日期to_char(monday+3,'DD') Thursday,--周四号to_char(monday+3,'YYYY-MM-DD') AS ThursdayWhole,--周四日期to_char(monday+4,'DD') Friday,--周五号to_char(monday+4,'YYYY-MM-DD') AS FridayWhole,--周五日期to_char(monday+5,'DD') Saturday,--周六号to_char(monday+5,'YYYY-MM-DD') AS SaturdayWhole,--周六日期to_char(sunday,'DD')   sunday,--周日号to_char(sunday,'YYYY-MM-DD') AS SundayWhole--周日日期
from 
(
select  to_char(sysdate,'yyyy') || monday.the_week week,decode(sign(monday.the_day - sunday.the_day),-1,monday.the_day,monday.the_day - 6) monday,sunday.the_day sundayfrom (select to_char(wwm, 'WW') the_week,to_char(wwm, 'D') the_daynum,wwm the_dayfrom (select trunc(trunc(sysdate,'yy'), 'MM') +rownum - 1 as wwmfrom user_objects--此处就是找一个大于366笔记录的临时表，随便哪个表都行where rownum < 366)where to_char(wwm, 'D') = 1) monday,(select to_char(wwm, 'WW') the_week,to_char(wwm, 'D') the_daynum,wwm + 1 the_dayfrom (select trunc(trunc(sysdate,'yy'), 'MM') +rownum - 1 as wwmfrom user_objectswhere rownum < 366)where to_char(wwm, 'D') = 7) sundaywhere monday.the_week = sunday.the_week) WHERE  to_char(monday,'MM') =to_char(sysdate,'MM') OR to_char(sunday,'MM') =to_char(sysdate,'MM');

SQL结果如下：

最后的看板效果如下：