How Can I Efficiently Generate a Series of Dates in T-SQL?

Efficiently generate T-SQL date sequence

In various data processing tasks, it is often necessary to create a date result set within a specified range. For example, you need to generate a list of dates between a start date and an end date.

Loop-based method

A common way to generate a date sequence is to use a WHILE loop, as shown below:

<code class="language-sql">-- 此方法效率低下，尤其是在处理大范围日期时。</code>

However, this approach is inefficient, especially when dealing with large ranges of dates.

Use a number table

A more efficient solution is to utilize the MASTER.DBO.SPT_VALUES table, which provides a sequence of numbers from 1 to 2047. By using this table with the DATETADD function, you can generate a set of dates within a specified range using the following query:

<code class="language-sql">DECLARE @dt datetime, @dtEnd datetime
SET @dt = GETDATE()
SET @dtEnd = DATEADD(DAY, 100, @dt)

SELECT DATETADD(DAY, number, @dt)
FROM (SELECT number FROM MASTER.DBO.SPT_VALUES WHERE TYPE = 'P') AS n
WHERE DATETADD(DAY, number, @dt) < @dtEnd</code>

This query generates a result set of dates from @dt to @dtEnd-1.

SQL Server 2008 and later features

SQL Server 2008 introduced the GENERATE_SERIES function, which can generate numeric sequences. This function can be used to generate a date result set as follows (SQL Server 2008 or later):

<code class="language-sql">DECLARE @Start datetime, @End datetime
SET @Start = '2024-01-01'
SET @End = '2024-12-31'

SELECT DATEADD(DAY, n, @Start)
FROM (SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1) n = ROW_NUMBER() OVER (ORDER BY object_id)) AS SubTable
ORDER BY n</code>

This query returns a result set of dates from @Start to @End (inclusive).

By using these techniques, you can efficiently generate result sets of increasing dates within a user-defined range in T-SQL.

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