T-SQL Trick: Use CTE to Render Dates In The Specified Date Range

 

Using a traditional programming language like C# or Python you can use “for” and “while” loop constructs to generate a sequence of numbers, dates, string values or anything else super-easily. But here we’ll learn how to generate date values in a specific range using Transact-SQL query. We’ll not use any SQL stored procedure.

Let’s suppose we want to generate 15 date values starting from today. We’ll use CTE (Common Table Expression) provided by T-SQL. A Common Table Expression is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can use CTE in various scenarios in order to achieve complex results. Below is the example T-SQL code which serves the mentioned purpose.

 

DECLARE @startDate datetime,  
        @endDate datetime;  
  
SELECT  @startDate = getdate(),  
        @endDate = getdate()+15;  

WITH myCTE AS  
   (  
	SELECT @startDate StartDate       
	UNION all  
       SELECT dateadd(DAY, 1, StartDate)      
	FROM  myCTE  
	WHERE dateadd(DAY, 1, StartDate) <=  @endDate    
  )  
SELECT Convert(varchar(10), StartDate, 105)  as StartDate from myCTE 

 

And here’s the result.

 

 

UNION and UNION ALL clauses in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both the tables, while UNION ALL returns all the records from both the tables.

Common Table Expression can be used in a T-SQL query in so many ways to extract data which is impossible or extremely difficult to attain without using a stored procedure or a traditional programming language. One usual scenario could be to join the results of the primary SQL query with results coming from one or more CTE expressions and that is a big deal indeed.