How to Generate a Series of Incrementing Dates in T-SQL?

Generating a Sequence of Dates in T-SQL: Efficient Methods

This article explores efficient techniques for creating a series of consecutive dates within a specified range using T-SQL, compatible with SQL Server versions 2005 and later. While a WHILE loop is a possible approach, it can be less efficient for larger date ranges. A superior method utilizes the spt_values system table.

For date ranges up to 2047 days, the following T-SQL query provides an effective solution:

<code class="language-sql">DECLARE @startDate DATETIME, @endDate DATETIME;
SET @startDate = GETDATE();
SET @endDate = DATEADD(day, 100, @startDate);

SELECT DATEADD(day, number, @startDate) AS IncrementalDate
FROM (SELECT number FROM master.dbo.spt_values WHERE [type] = 'P') AS NumberSequence
WHERE DATEADD(day, number, @startDate) < @endDate;</code>

This query leverages the numerical sequence within spt_values to generate the date series. It adds each number in the sequence to the @startDate, effectively creating incrementing dates. The WHERE clause ensures the generated dates fall within the desired range, defined by @startDate and @endDate. This approach avoids the performance limitations often associated with iterative WHILE loop methods.

The above is the detailed content of How to Generate a Series of Incrementing Dates in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!