在 TSQL 中生成递增日期的结果集 [英] Generate a resultset of incrementing dates in TSQL

问题描述

考虑需要创建日期结果集.我们有开始日期和结束日期，我们想生成一个介于两者之间的日期列表.

Consider the need to create a resultset of dates. We've got start and end dates, and we'd like to generate a list of dates in between.

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (@Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'

--need to fill @AllDates. Trying to avoid looping. 
-- Surely if a better solution exists.

考虑带有 WHILE 循环的当前实现:

Consider the current implementation with a WHILE loop:

DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
 INSERT INTO @AllDates VALUES (@dCounter)
 SELECT @dCounter=@dCounter+1 
END

问题:您将如何使用 T-SQL 创建一组在用户定义范围内的日期?假设 SQL 2005+.如果您的答案是使用 SQL 2008 功能，请标记为此类.

Question: How would you create a set of dates that are within a user-defined range using T-SQL? Assume SQL 2005+. If your answer is using SQL 2008 features, please mark as such.

推荐答案

如果您的日期相隔不超过 2047 天:

If your dates are no more than 2047 days apart:

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

在多次请求后，我更新了我的答案.为什么?

原始答案包含子查询

 select distinct number from master.dbo.spt_values
     where name is null

结果与我在 SQL Server 2008、2012 和 2016 上的测试结果相同.

which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.

然而，当我试图在从 spt_values 查询时分析 MSSQL 内部的代码时，我发现 SELECT 语句总是包含子句 WHERE [type]='[魔法代码]'.

However, as I tried to analyze the code that MSSQL internally when querying from spt_values, I found that the SELECT statements always contain the clause WHERE [type]='[magic code]'.

因此我决定虽然查询返回正确的结果，但由于错误的原因它提供了正确的结果:

Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:

未来版本的 SQL Server 可能会定义不同的 [type] 值，该值也将 NULL 作为 [name] 的值>，在 0-2047 范围之外，甚至不连续，在这种情况下，结果将是完全错误的.

There may be a future version of SQL Server which defines a different [type] value which also has NULL as values for [name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.

这篇关于在 TSQL 中生成递增日期的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！