CODEWITHSUNDEEP
sqlsql-server
Morteza
Morteza

Reputation: 191

select all dates between two date column in table

I have a table like this:

Id From To
1 2018-01-28 2018-02-01
2 2018-02-10 2018-02-12
3 2018-02-27 2018-03-01

How to get all dates between From and To dates like this?

FromDate
----------
2018-01-28 
2018-01-29  
2018-01-30  
2018-01-31  
2018-02-01  
2018-02-10
2018-02-11
2018-02-12
2018-02-27
2018-02-28
2018-02-01

Upvotes: 3

Views: 5829

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Generate a calendar table containing all dates within, e.g. 2018, and then inner join that table to your current table:

DECLARE @todate datetime, @fromdate datetime
SELECT @fromdate='2018-01-01', @todate='2018-12-31'

;WITH calendar (FromDate) AS (
    SELECT @fromdate AS FromDate
    UNION ALL
    SELECT DATEADD(day, 1, FromDate)
    FROM Calendar
    WHERE FromDate < @todate
)

SELECT t1.FromDate
FROM calendar t1
INNER JOIN yourTable t2
    ON t1.FromDate BETWEEN t2.[From] AND t2.[To];

Demo

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you don't have that many dates, a recursive CTE is a pretty easy approach:

with cte as (
      select id, fromdate as dte, fromdate, todate
      from t
      union all
      select id, dateadd(day, 1, dte), fromdate, todate
      from cte
      where dte < todate
     )
select id, dte
from cte;

A recursive CTE has a default "depth" of 100. That means that it will work for spans up to 100 dates long (for each id). You can override this with the MAXRECURSION option.

It is usually slightly more efficient to do this with some sort of numbers table. However, recursive CTEs are surprisingly efficient for this sort of calculation. And this is a good way to start learning about them.

Upvotes: 2

Related Questions