Happy Holidays everyone! How many ways can you write a sql to print out the new year calendar? I will accept the answer with least code (after removing spaces) on New Year's Day.
Below is an example I created in 2008 with 10g (you can see some old school skills since pivot and listagg were not available):
var p_year number;
exec :p_year := 2026;
alter session set nls_territory='AMERICA';
with d as(
select to_char(dt,'mm') mm
,lpad(' '||max(decode(to_char(dt,'d'),'1',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'2',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'3',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'4',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'5',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'6',to_char(dt,'fmdd'))),4)
||lpad(' '||max(decode(to_char(dt,'d'),'7',to_char(dt,'fmdd'))),4) as days
,dense_rank() over(partition by to_char(dt,'mm') order by min(dt)) num_of_week
,rpad(to_char(min(dt),'Month'),28) as mon
from (select to_date(:p_year||'0101','yyyymmdd')+rownum-1 dt
from dual
connect by rownum <= 366
)
where to_char(dt,'yyyy')=:p_year
group by to_char(dt,'mm'), to_char( dt+1, 'iw' )
)
,cal as (
select mm,days,num_of_week from d
union all select mm,lpad(' ',28) as days, 5 as num_of_week from d group by mm having max(num_of_week)<5
union all select mm,lpad(' ',28) as days, 6 as num_of_week from d group by mm having max(num_of_week)<6
union all select mm,rpad(min(mon),28),-1 from d group by mm
union all select mm,' Sun Mon Tue Wed Thu Fri Sat',0 from d group by mm
)
select replace(sys_connect_by_path(days,'**'),'*',' ') as calendar
from cal
where mm in ('03','06','09','12')
start with mm in ('01','04','07','10')
connect by to_number(mm)=to_number(prior mm)+1 and num_of_week = prior num_of_week and level<=3
order by mm,num_of_week
;
January February March
Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
1 2 3 1 2 3 4 5 6 7 1 2 3 4 5 6 7
4 5 6 7 8 9 10 8 9 10 11 12 13 14 8 9 10 11 12 13 14
11 12 13 14 15 16 17 15 16 17 18 19 20 21 15 16 17 18 19 20 21
18 19 20 21 22 23 24 22 23 24 25 26 27 28 22 23 24 25 26 27 28
25 26 27 28 29 30 31 29 30 31
April May June
Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 1 2 1 2 3 4 5 6
5 6 7 8 9 10 11 3 4 5 6 7 8 9 7 8 9 10 11 12 13
12 13 14 15 16 17 18 10 11 12 13 14 15 16 14 15 16 17 18 19 20
19 20 21 22 23 24 25 17 18 19 20 21 22 23 21 22 23 24 25 26 27
26 27 28 29 30 24 25 26 27 28 29 30 28 29 30
31
July August September
Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 1 1 2 3 4 5
5 6 7 8 9 10 11 2 3 4 5 6 7 8 6 7 8 9 10 11 12
12 13 14 15 16 17 18 9 10 11 12 13 14 15 13 14 15 16 17 18 19
19 20 21 22 23 24 25 16 17 18 19 20 21 22 20 21 22 23 24 25 26
26 27 28 29 30 31 23 24 25 26 27 28 29 27 28 29 30
30 31
October November December
Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
1 2 3 1 2 3 4 5 6 7 1 2 3 4 5
4 5 6 7 8 9 10 8 9 10 11 12 13 14 6 7 8 9 10 11 12
11 12 13 14 15 16 17 15 16 17 18 19 20 21 13 14 15 16 17 18 19
18 19 20 21 22 23 24 22 23 24 25 26 27 28 20 21 22 23 24 25 26
25 26 27 28 29 30 31 29 30 27 28 29 30 31