하얀나다의블로그

달력

it/SQL 2015. 9. 7. 16:38 Posted by 하얀나다

오라클로 달력 만들기

본문 : http://www.gurubee.net/article/22424

with t as (

select to_date('201509','yyyymm') ym from dual)

SELECT * FROM (

SELECT MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '1', LEVEL)) SUN

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '2', LEVEL)) MON

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '3', LEVEL)) TUE

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '4', LEVEL)) WED

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '5', LEVEL)) THU

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '6', LEVEL)) FRI

, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '7', LEVEL)) SAT

FROM T

CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1

GROUP BY TRUNC (YM + LEVEL, 'iw')

ORDER BY 7)