获取 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屋！