Get all dates in date range in SQL Server

Question

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.

Are you trying to get all valid dates between FromDate and ToDate for each Id ? I'm unsure what you're trying to accomplish. — F43G4N, Feb 04 '12 at 10:57

score 4 · Answer 1 · answered Feb 04 '12 at 11:40

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

score 4 · Accepted Answer · edited Jan 18 '21 at 12:30

4

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

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 04 '12 at 19:36

Mikael Eriksson

136,425
22
210
281

@JCooper Nope. The numbers table starts with `0` Check the link. It will give the expected output starting with `FromDate` ending with `EndDate`. – Mikael Eriksson Feb 04 '12 at 20:25
I ran the query using the link, your results go from 11-09 - 11-11 instead of 11-10 to 11-12, other than that +1 for the alternative approach – J Cooper Feb 04 '12 at 20:29
@JCooper - That is indeed strange. When I click the link it shows 10 to 12 and 12 to 14. I wonder why it is different. – Mikael Eriksson Feb 04 '12 at 20:32
@JCooper - what timezone are you in? I am in GMT+1 and the dates have a time part and there should not be one. If you are in GMT-x It would explain why you see dates -1. There is something funky going on with SE-Data. – Mikael Eriksson Feb 04 '12 at 20:49
I am GMT-5 (US Eastern), didn't even occur to me, makes sense though – J Cooper Feb 04 '12 at 20:59
@MikaelEriksson, So should I go ahead with one. This seems exactlly what I want and I can also `UNION ALL` this with some query. I hope I'll not run into problems using `master.dbo.spt_values`. – Soham Dasgupta Feb 06 '12 at 05:16
1

@SohamDasgupta - `spt_values` is numbered from 0 to 2047. If you don't you expect to have one date range longer than 2047 days you should be fine. You could also create a numbers table of your own. It will be good to have around in more than one occasion. Make sure the first number is 0. Please have a look at [this article](http://www.sqlservercentral.com/articles/T-SQL/62867/) by Jeff Moden at SQLServerCentral.com – Mikael Eriksson Feb 06 '12 at 06:31

score 1 · Answer 3 · answered Feb 04 '12 at 11:40

1

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

answered Feb 04 '12 at 11:40

Rubens Farias

57,174
8
131
162

Can this be done without CTE, as I have to union all this query with some other query. Also what If I don't require the `ID` column. – Soham Dasgupta Feb 04 '12 at 11:57
You can use joins with CTE queries; can you give me an example? If you don't need that ID column, drop it -- I just thought you could be interested to understand date sources when you get two overlapping ranges, as you'll get 'duplicated' dates – Rubens Farias Feb 04 '12 at 12:13
Yes that is exactlly what I encountered. A duplicate row if I have same dates on both sides and no `ID` column. – Soham Dasgupta Feb 04 '12 at 12:23
how do you deal with duplicate dates? you can just ignore them by using `DISTINCT`, or you can uncomment that `--*` to see what's going on – Rubens Farias Feb 04 '12 at 12:35

Get all dates in date range in SQL Server

3 Answers3

Linked

Related