在SQL Server中获取日期范围内的所有日期

问题描述：

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

I got this example from one * 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中获取日期范围内的所有日期

相关推荐