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