oracle 달력 sql

oracle 달력 sql

 

select sun,
       mon,
       tue,
       wed,
       thu,
       fri,
       sat
  from (
  select week,
               max(decode(dat, 1, day)) as sun,
               max(decode(dat, 2, day)) as mon,
               max(decode(dat, 3, day)) as tue,
               max(decode(dat, 4, day)) as wed,
               max(decode(dat, 5, day)) as thu,
               max(decode(dat, 6, day)) as fri,
               max(decode(dat, 7, day)) as sat
          from (

                select to_char(dates, 'DD') as day,
                        to_char(dates, 'D') as dat,
                        to_char(dates, 'D') - to_char(dates, 'DD') as week
                  from (

                         select to_date(to_char(sysdate, 'yyyymm') || lpad(level, 2, '0')) as dates
                           from dual
                         connect by to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyy-mm-dd') + level - 1 <= last_day(sysdate)

                         )

                )
         group by week
         )
 order by nvl(sun, ' '),
       nvl(mon, ' '),
       nvl(tue, ' '),
       nvl(wed, ' '),
       nvl(thu, ' '),
       nvl(fri, ' '),
       nvl(sat, ' ')

 

월기준 주차 temp

select wk,
       to_char(dd - (wk * 7) + 7) as dy

  from (
        
        select to_char(sysdate, 'yyyymm') as yymm,
               trunc(1 + ((level - 1) / 7)) as wk,
                level as dd
          from dual
        connect by level <= 42
        
        )

 

월기준 주차

select ceil((to_number(substr(to_char(sysdate, 'yyyymmdd'), -2, 2)) + 7 - to_number(to_char(sysdate, 'D'))) / 7) as wk
  from dual