Build a T-SQL Calendar using SQL Function GetFullMonth on MS SQL Server

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')



t-sql calendar GetFullMonth function

Popular posts from this blog

Using SSRS web services to render a report as a PDF

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

Creating a Link Server to MYSQL from MSSQL