query to get calender in Oracle SQL

Question

suppose if i give '12345'

output should be:

1
12
123
1234
12345
1234
123
12
1

Answer 1

I'm not sure how to write in SQL query but we can achieve in PLSQL:

create or replace procedure calender(p_date VARCHAR2) as
C NUMBER;
BEGIN
C := TO_NUMBER (TO_CHAR (TRUNC (TO_DATE (p_date,'MM-YYYY'), 'MM'), 'D'));
DBMS_OUTPUT.put_line ('|Sun Mon Tue Wed Thu Fri Sat');
DBMS_OUTPUT.put ('|' || RPAD (' ', (c - 1) * 4, ' '));
FOR i IN 1 .. TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (p_date,'MM-YYYY')), 'DD'))
LOOP
IF MOD (C + i - 2, 7) = 0
THEN
DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put ('|');
END IF;
DBMS_OUTPUT.put (' ' || TO_CHAR (i, 'fm00') || ' ');
END LOOP;
DBMS_OUTPUT.new_line;
END;

Input:
FEB-2014

Output:
|Sun Mon Tue Wed Thu Fri Sat
| 01
| 02 03 04 05 06 07 08
| 09 10 11 12 13 14 15
| 16 17 18 19 20 21 22
| 23 24 25 26 27 28

Answer 2

SET DATEFIRST 7;

DECLARE @Date DATETIME = '20150101';
DECLARE @Dates TABLE 
(   Date DATE, MonthNum INT, 
    MonthName VARCHAR(15), 
    DayNum INT, 
    MonthStart DATE, 
    Day VARCHAR(2)
);

WITH Dates AS
(   SELECT  [Date] = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY Object_ID) - 8, @Date)
    FROM    sys.All_Objects
)
INSERT @Dates (Date, MonthNum, MonthName, DayNum, MonthStart, Day)
SELECT  Date,
        [MonthNum] = DATEPART(MONTH, Date),
        [MonthName] = LEFT(DATENAME(MONTH, Date), 3), 
        [DayNum] = DATEPART(WEEKDAY, Date),
        [MonthStart] = DATEADD(MONTH, DATEDIFF(MONTH, 0, d1.Date), 0),
        [Day] = CONVERT(VARCHAR(2), DATEPART(DAY, Date))
FROM    Dates d1
WHERE   Date < DATEADD(YEAR, 1, @Date);

WITH Calendar AS
(   SELECT  MonthName, 
            MonthNum, 
            [Identifier] = CONVERT(VARCHAR(1),  DayNum) + 
                            CONVERT(VARCHAR(1),  Occurance), 
            [Day]
    FROM    @Dates d1
            CROSS APPLY
            (   SELECT  [Occurance] = COUNT(*) 
                FROM    @Dates d2
                WHERE   d2.Date >= DATEADD(DAY, 1 - DATEPART(WEEKDAY, d1.MonthStart), d1.MonthStart) 
                AND     d2.Date <= d1.Date
                AND     d1.DayNum = d2.DayNum
            ) o
    WHERE   d1.Date >= @Date
    UNION ALL
    SELECT  [MonthName] = LEFT(DATENAME(MONTH, '2015' + 
                                                RIGHT('0' + CONVERT(VARCHAR(2), MonthNumber), 2) + 
                                                '01'), 3), 
            MonthNumber, 
            [ID] = DayNumber + Occurance, 
            [Day] = ''
    FROM    (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7')) AS d (DayNumber)
            CROSS JOIN (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6')) AS o (Occurance)
            CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS m (MonthNumber)
)
SELECT  [MonthName],
        [Sun] = [11], [Mon] = [21], [Tues] = [31], [Wed] = [41], [Thu] = [51], [Friday] = [61], [Sat] = [71],
        [Sun] = [12], [Mon] = [22], [Tues] = [32], [Wed] = [42], [Thu] = [52], [Friday] = [62], [Sat] = [72],
        [Sun] = [13], [Mon] = [23], [Tues] = [33], [Wed] = [43], [Thu] = [53], [Friday] = [63], [Sat] = [73],
        [Sun] = [14], [Mon] = [24], [Tues] = [34], [Wed] = [44], [Thu] = [54], [Friday] = [64], [Sat] = [74],
        [Sun] = [15], [Mon] = [25], [Tues] = [35], [Wed] = [45], [Thu] = [55], [Friday] = [65], [Sat] = [75],
        [Sun] = [16], [Mon] = [26], [Tues] = [36], [Wed] = [46], [Thu] = [56], [Friday] = [66], [Sat] = [76]
FROM    Calendar
        PIVOT
        (   MAX([Day])
            FOR [Identifier] IN
            (   [11], [21], [31], [41], [51], [61], [71],
                [12], [22], [32], [42], [52], [62], [72],
                [13], [23], [33], [43], [53], [63], [73],
                [14], [24], [34], [44], [54], [64], [74],
                [15], [25], [35], [45], [55], [65], [75],
                [16], [26], [36], [46], [56], [66], [76]
            )
        ) AS pvt

In the above query to show the calendar of 2015