sql 日历

/* Formatted on 2013/9/7 20:45:28 (QP5 v5.185.11230.41888) */
SELECT MIN (DECODE (TO_CHAR (DAYS, 'D'), 1, TO_CHAR (DAYS, 'FMDD'))) MON,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 2, TO_CHAR (DAYS, 'FMDD'))) TUE,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 3, TO_CHAR (DAYS, 'FMDD'))) WED,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 4, TO_CHAR (DAYS, 'FMDD'))) THU,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 5, TO_CHAR (DAYS, 'FMDD'))) FRI,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 6, TO_CHAR (DAYS, 'FMDD'))) SAT,
MIN (DECODE (TO_CHAR (DAYS, 'D'), 7, TO_CHAR (DAYS, 'FMDD'))) SUN
FROM (SELECT (A.BASE_MON + ROWNUM - 1) AS DAYS,
A.BASE_MON,
( ( TRUNC (BASE_MON + ROWNUM - 1, 'D')
- TRUNC (TRUNC (BASE_MON + ROWNUM - 1, 'Y'), 'D'))
/ 7
+ 1)
AS WEEK_NUM
FROM (SELECT TO_DATE ('201202', 'YYYYMM') BASE_MON FROM DUAL) A,
DICTIONARY B) C
WHERE C.DAYS <= LAST_DAY (BASE_MON)
GROUP BY WEEK_NUM
ORDER BY WEEK_NUM;

 

 

 

==============================================

select dates,
weekday,
decode(weekday,'1','일','2','월','3','화','4','수','5','목','6','금','7','토') "weekday(요일)",
week_cnt "week_cnt(주차)"
from (
select to_date(days,'yyyymmdd') as dates,
to_char(to_date(days,'yyyymmdd'), 'd') as weekday,
week_cnt
from (select to_char(months,'yyyymm') as months,
to_char(months,'yyyymm') || lpad(lv, 2, '0') as days,
day_cnt,
(lv + (7-to_char(months+(lv-1), 'd')) + (to_char(to_date(to_char(months, 'yyyymm')||'01', 'yyyymmdd'), 'd')-1)) / 7 as week_cnt
from (select add_months(to_date(201101,'yyyymm'), lv - 1) as months,
to_char(last_day(add_months(to_date(201101,'yyyymm'), lv-1)), 'dd') as day_cnt
from dual sa,
(select level lv
from dual
connect by level <= months_between(to_date(201112,'yyyymm'), to_date(201101,'yyyymm'))+1)
) a,
(select level lv
from dual
connect by level <= 31
) b
) m
where months||day_cnt >= days
order by months, days
)

转载于:https://www.cnblogs.com/kevinkim/p/3307752.html