Build a T-SQL Calendar using SQL Function GetFullMonth on MS SQL Server
User Defined SQL Function GetFullMonth
SQL Server t-sql developers or administrators can use the below user defined function in order to list the calendar dates within a month.
We can pass the date as a parameter to the t-sql calendar date function and list the sql dates which belongs to the month of the given parameter date value.
You can alter the following t-sql function code as you wish in order to your requirements. For example, you can only pass the month.
You can further alter the function to pass two date parameter values and list the calendar dates within the given sql date range instead of listing only dates in a given month.
For this t-sql task you can use the dbo.DateTable function which is demonstrated at How to Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code titled article.
CREATE FUNCTION GetFullMonth
(
@d datetime
) RETURNS @days TABLE
(
[date] datetime,
[days] varchar(20)
)
BEGIN
DECLARE @d1 datetime, @d2 datetime, @d3 datetime
SELECT
@d1 = DATEADD(mm, DATEDIFF(mm, 0, @d), 0),
@d2 = DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @d) + 1, 0))
WHILE @d1 <= @d2
BEGIN
INSERT INTO @days SELECT @d1, DATENAME(DW, @d1)
SELECT @d1 = DATEADD(dd, 1, @d1)
END
RETURN
END
And the output of the dbo.GetFullMonth function can be seen with a sample t-sql SELECT statement as follows:
SELECT * FROM dbo.GetFullMonth('20080229')
How to Query SQL Server for T-SQL Calendar using GetFullMonth using PIVOT
After listing sql calendar date values in a month as rows, we need to display them now in a format similar to frequently used in printed calendars.
The day names are listed as column names and dates are shown under the related columns.
The first solution is supplied by Plamen Ratchev from SQLStudio.com using the t-sql PIVOT. Since I liked the t-sql code he coded much I want to share it with you here.
SELECT
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
FROM
(
SELECT
DATEPART(WEEK, [date]) AS week_number,
[date],
[days]
FROM dbo.getFullmonth('20080229')
) AS Dates
PIVOT
(
MIN([date])
FOR
[days] IN (
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
)
) AS Pivots
And the output of this sql pivot statement will return the following result set as output which is displaying sql dates in a calendar format.
Alternative Method for Building SQL Calendar Week Based using CTE
I have also developed a solution of mine, but using simple t-sql CTE instead of t-sql PIVOT statements.
Both methods are actually ending with the same result set.
Here is the sample t-sql code for the t-sql calendar listing.
WITH CTE AS
(
SELECT
wd = DATEPART(WEEK, [date]),
[date],
Sunday = CASE WHEN [days] = 'Sunday' THEN [date] ELSE NULL END,
Monday = CASE WHEN [days] = 'Monday' THEN [date] ELSE NULL END,
Tuesday = CASE WHEN [days] = 'Tuesday' THEN [date] ELSE NULL END,
Wednesday = CASE WHEN [days] = 'Wednesday' THEN [date] ELSE NULL END,
Thursday = CASE WHEN [days] = 'Thursday' THEN [date] ELSE NULL END,
Friday = CASE WHEN [days] = 'Friday' THEN [date] ELSE NULL END,
Saturday = CASE WHEN [days] = 'Saturday' THEN [date] ELSE NULL END
FROM dbo.getFullmonth ('20080229')
)
SELECT
MAX(Sunday) Sunday,
MAX(Monday) Monday,
MAX(Tuesday) Tuesday,
MAX(Wednesday) Wednesday,
MAX(Thursday) Thursday,
MAX(Friday) Friday,
MAX(Saturday) Saturday
FROM CTE
GROUP BY wd
Here is the CTE output for generating sql calendar dates on a MS SQL Server 2005 or later (SQL Server 2008).
