oracle function last_day和connect by實現列印一個月每天

wisdomone1發表於2013-05-27

SQL> select to_date(to_char(sysdate,'yyyy-mm')||'_'||level,'yyyy-mm-dd') as everyday from dual connect by  level<=to_char(last_day(sysdate),'dd') order by 1;
 
EVERYDAY
-----------
2013-05-01
2013-05-02
2013-05-03
2013-05-04
2013-05-05
2013-05-06
2013-05-07
2013-05-08
2013-05-09
2013-05-10
2013-05-11
2013-05-12
2013-05-13
2013-05-14
2013-05-15
2013-05-16
2013-05-17
2013-05-18
2013-05-19
2013-05-20
 
EVERYDAY
-----------
2013-05-21
2013-05-22
2013-05-23
2013-05-24
2013-05-25
2013-05-26
2013-05-27
2013-05-28
2013-05-29
2013-05-30
2013-05-31
 
31 rows selected

小結:

              1,last_day提取當月的最後一天

              2,to_char提取當月最後一天為數值

              3,connect by level=上述的2產生的值,這樣即可產生當月對應天數的記錄

             4,select先用to_char分割開,各為yyyy-mm和dd,最後用to_date把二者整合

             5,order by 1即對上述4產生的列進行排序輸出結果

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-762138/,如需轉載,請註明出處,否則將追究法律責任。

相關文章