Skip to main content

Alternative method of querying the calendar using the CTE method

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).
t-sql cte command to format dates as calendar

Comments

Post a Comment

Popular posts from this blog

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...
Post a Comment
Read more

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 @...
Post a Comment
Read more

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

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it. First things first you will need a merchant account with ANZ who will give you the following ·          Merchant Account ·          User name ·          password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details. Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide. What you will need for the implementation would...
3 comments
Read more