Skip to main content

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

Comments

Popular posts from this blog

Removing HTML Tags from Text Using SQL Server User-Defined Function

  Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT   DECLARE @End INT   DECLARE @Length INT   SET @Start = CHARINDEX( '<' , @HTMLText )  SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText ))  SET @Length = ( @End - @Start ) + 1   WHILE @Start > 0 AND @...

Troubleshooting Database Mail: General steps

SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005 Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabled In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code: Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1 . If the run_value is not 1 , Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration . If you decide that it is appropriate to enable Database Mail, execute the fo...

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using t...