在SQL Server中获取日期范围内的所有日期 [英] Get all dates in date range in SQL Server

问题描述

我从一个StackOverflow问题中得到了这个例子，该问题被提出，但是根据需要我无法得到它。

I got this example from one StackOverflow question that was asked but I couldn't get it work according to my need.

WITH DateTable
AS
(
    SELECT CAST('20110101' as Date) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE]) FROM DateTable 
    WHERE DATEADD(dd, 1, [DATE]) < cast('20110131' as Date)
)
SELECT dt.[DATE] FROM [DateTable] dt

输入 -

ID |   FromDate  | ToDate
=============================
1  |  2011-11-10 | 2011-11-12
2  |  2011-12-12 | 2011-12-14

输出 -

SN |   Dates     | 
==================
1  |  2011-11-10 | 
2  |  2011-11-11 | 
3  |  2011-11-12 | 
4  |  2011-12-12 | 
5  |  2011-12-13 | 
6  |  2011-12-14 |

请参阅此代码对静态日期工作正常。但在我的情况下，我有一个包含三列 Id，FromDate，ToDate 的表。现在我想将每一行的每个范围都转换为单独的日期。

See this code works fine for static dates. But in my case I have a table containing three columns Id, FromDate, ToDate. Now I want to convert each range in the every row to individual dates.

如果范围来自表格，我无法得到上面的例子来工作，显然这查询必须为范围表中的每一行运行，这是另一个令人困惑的挑战。

I cannot get the above example to work in case if the range comes from the table and obviously this query has to run for every row in the range table, which is another confusing challenge.

请帮助。

推荐答案

有一个数字表的帮助。

declare @T table
(
  ID int identity primary key,
  FromDate date,
  ToDate date
)

insert into @T values
('2011-11-10', '2011-11-12'),
('2011-12-12', '2011-12-14')

select row_number() over(order by D.Dates) as SN,
       D.Dates
from @T as T
  inner join master..spt_values as N
    on N.number between 0 and datediff(day, T.FromDate, T.ToDate)
    cross apply (select dateadd(day, N.number, T.FromDate)) as D(Dates)
where N.type ='P'

尝试 SE数据

这篇关于在SQL Server中获取日期范围内的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！