How Can I Efficiently Generate a Date Range Resultset in T-SQL?

Generating a Date Series in T-SQL

This article demonstrates efficient techniques for creating a result set containing a sequence of dates within a given range using T-SQL. We'll explore methods for generating this date series, focusing on optimized approaches.

Defining the Date Range

Let's establish our start and end dates:

<code class="language-sql">DECLARE @StartDate datetime = 'Mar 1 2009', @EndDate datetime = 'Aug 1 2009'</code>

A Loop-Based Approach (Less Efficient)

A straightforward, but less efficient, method utilizes a WHILE loop:

<code class="language-sql">DECLARE @currentDate datetime
SET @currentDate = @StartDate
WHILE @currentDate <= @EndDate
BEGIN
    SELECT @currentDate
    SET @currentDate = DATEADD(day, 1, @currentDate)
END</code>

This iterative approach works, but it can be slow for large date ranges.

Optimized Approach using spt_values (SQL Server 2005 and later)

For improved performance, especially with wider date ranges, leverage the spt_values system table (available in SQL Server 2005 and later versions):

<code class="language-sql">DECLARE @StartDate datetime, @EndDate datetime;
SET @StartDate = GETDATE();
SET @EndDate = DATEADD(day, 100, @StartDate);

SELECT DATEADD(day, number, @StartDate) AS DateValue
FROM master..spt_values
WHERE type = 'P'
  AND DATEADD(day, number, @StartDate) <= @EndDate;</code>

This query efficiently generates the date series by adding sequential numbers from spt_values to the start date and filtering to include only dates within the specified range. This method is significantly faster than the loop-based approach for larger datasets. The master..spt_values table provides a readily available sequence of numbers, making it ideal for this task.

The above is the detailed content of How Can I Efficiently Generate a Date Range Resultset in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!