问题描述
考虑需要创建日期结果集.我们有开始日期和结束日期，我们想生成一个介于两者之间的日期列表.
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 中生成递增日期的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！