달력 1

환경 : 오라클 12C

 

WITH MONTH_DAY

AS

(

SELECT ( TRUNC( TO_DATE( :AV_DAY , 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + LEVEL - 1 DD

FROM DUAL

CONNECT BY LEVEL <= ( ( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ) + ( 7 - TO_CHAR( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ), 'D' ))) -- 종료일

- ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + 1 ) -- 시작일

)

, MONTH_DAY_WEEK

AS

(

SELECT DD

, TRUNC( (TO_NUMBER(DD - ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) ))) - 1 )/ 7) WEEK

, TO_CHAR( DD, 'DY' ) DAYOFWEEK

, TO_CHAR( DD, 'DD' ) VAL1

FROM MONTH_DAY

)

SELECT TO_CHAR( MIN(DD), 'YYYYMMDD') || '~' || TO_CHAR( MAX( DD ), 'YYYYMMDD') FROMTO

, WEEK

, MAX("'일'") "SUN"

, MAX("'월'") "MON"

, MAX("'화'") "TUE"

, MAX("'수'") "WED"

, MAX("'목'") "THU"

, MAX("'금'") "FRI"

, MAX("'토'") "SAT"

FROM MONTH_DAY_WEEK

PIVOT

(

MAX( VAL1 ) FOR DAYOFWEEK IN ( '일', '월', '화', '수', '목', '금', '토' )

) A

GROUP BY WEEK

ORDER BY WEEK

;

 

:AV_DAY := '20191001'

 

달력이 표시됨.