SQL Query to generate CALENDAR for Year 2017

SQL is not only an  " Art of Generating Data " ,  but also an " Art of Showing Data "

We all know about date functions in SQL,  can we do create a calendar for year 2017.. using Date functions .. and also for years .. 2016..2018...1823...2957.. ?

YES.. WE CAN..  using SQL..

First we will see the Query to show the CALENDAR for year  2017.

Try executing the below queries in your SQL developer or TOAD by just changing year as you want. 

Calendar SQL Statement for 2017 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-2017','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2

 

Oh.. I want to see my year of birth.. say for eg., 1991

Calendar SQL Statement for 1991 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-1991','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2

 

Around 1000 years before :-

Calendar SQL Statement for 1015 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-1015','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2 

After 1000 plus year and so on..

Calendar SQL Statement for 3742 AD :-

WITH TT AS (SELECT (TO_DATE('01-Jan-3742','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2 

 

##  Kindly feel free to comment your feedback  ##