How to Efficiently Generate a Sequence of Dates in T-SQL?

Generate incremental date result set in T-SQL

In SQL Server, generating a date set between a specified start date and end date requires an efficient and optimized solution. A common approach is to use a WHILE loop to incrementally add a day to the start date until the end date is reached.

However, for cases where the date range is within 2047 days, another method can be used. The following T-SQL query uses the spt_values ​​system table:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from (select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @dt) < @dtEnd

This query utilizes the spt_values ​​table, which contains the range of numbers from 0 to 2047. By filtering the 'P' type we get a set of consecutive numbers. Each number is added to the starting date using the dateadd function, effectively producing a result set of incrementing dates.

This method has the following advantages:

• Simplicity: It requires no loops or complex calculations.
• Efficiency: Queries use system tables, avoiding the overhead associated with user-defined tables.
• Scalability: It can handle larger date ranges within the limit of 2047 days.

For SQL Server 2008 and above, this method provides an efficient and straightforward solution for generating increasing date sets in T-SQL.

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