Get all dates in date range in SQL Server

问题:

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

Input-

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

Output -

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 |

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.

Please help.

回答1:

With a little help of a numbers table.

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'

Try on SE Data

回答2:

create table Dates (Id int, FromDate date, ToDate date)
insert into Dates values (1, '2011-11-10', '2011-11-12')
insert into Dates values (2, '2011-12-12', '2011-12-14')

with DateTable as
(
    select FromDate as Dt, ToDate
    from Dates
    union all
    select DATEADD(D, 1, Dt), ToDate
    from DateTable
    where DATEADD(D, 1, Dt) <= ToDate
)
select ROW_NUMBER() over (order by Dt) as SN, Dt as Dates
from DateTable
order by Dt

回答3:

What about this?

--DROP TABLE #Test
CREATE TABLE #Test(ID int, FromDate datetime, ToDate datetime)
INSERT INTO  #Test VALUES (1, '2011-11-10', '2011-11-12')
INSERT INTO  #Test VALUES (2, '2011-12-12', '2011-12-14')

;
WITH DateTable
AS
(
    SELECT  ID, FromDate, ToDate, 0 AS Seed FROM #Test
    UNION   ALL
    SELECT  ID, DATEADD(dd, 1, FromDate), ToDate, Seed + 1
    FROM    DateTable
    WHERE   DATEADD(dd, 1, FromDate) <= ToDate
)
SELECT  --*
        ROW_NUMBER() OVER (ORDER BY ID, Seed) SN, FromDate AS Dates
FROM    DateTable