select dates between two dates with SQL Server

Question

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query.

id      state   start_date      end_date
------------------------------------------------------------------------
2   New     2016-02-24      2016-02-28 
2   Active  2016-02-28      2016-03-01 
2   New     2016-03-01      NULL
3   New     2016-02-23      2016-02-25 
3   Active  2016-02-25      2016-02-27 
3   New     2016-02-27      NULL

id      state   start_date      end_date
------------------------------------------------------------------------
2   New     2016-02-24      2016-02-25 
2   New     2016-02-25      2016-02-26
2   New     2016-02-26      2016-02-27
2   New     2016-02-27      2016-02-28
2   Active  2016-02-28      2016-02-29
2   Active  2016-02-29      2016-03-01
2   New     2016-03-01      NULL
3   New     2016-02-23      2016-02-24 
3   New     2016-02-24      2016-02-25
3   Active  2016-02-25      2016-02-26
3   Active  2016-02-26      2016-02-27 
3   New     2016-02-27      NULL

Pointing the mistake in your query, that you're not showing us? Contrary to popular belief, we're not mind readers (or at least, not all of the time) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 22, 2019 at 9:21
A simple BETWEEN works just fine with dates. It won't work if you use stringds instead of dates. You didn't post any query. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2019 at 9:21
If you want to return every date between the start_date and end_date join with a calendar table using ON Calendar.Date between start_date and end_date as the condition. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2019 at 9:23
To add to the above comment: Bones of SQL - The Calendar Table — Thom A
– Thom A ♦, Commented Jan 22, 2019 at 9:24
Also Temporal Data Techniques in SQL Server. Calendar tables and Numbers tables can convert many complex range or date problems into simple joins and range queries. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2019 at 9:30

PSK · Accepted Answer · 2019-01-22 09:49:03Z

3

If you don’t have calendar table available , you can try like following query using master..[spt_values] to generate the missing dates.

;WITH cte 
     AS (SELECT ( Row_number() 
                    OVER ( 
                      ORDER BY (SELECT NULL)) ) - 1 RN 
         FROM   master..[spt_values] T1) 
SELECT id, 
       state, Dateadd(day, rn, start_date)     AS start_date, 
       Dateadd(day, rn + 1, start_date) AS end_date 
FROM   <Table_Name> t1 
       INNER JOIN cte T2 
               ON Dateadd(day, rn, start_date) < t1.end_date

Note: Replace with appropriate table name.

edited Jan 22, 2019 at 9:49

answered Jan 22, 2019 at 9:37

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Guillot · Accepted Answer · 2019-01-22 10:23:24Z

0

If you need to get all the dates between two dates, the simplest way to do so is using a recursive common table expression (and you avoid depending on undocumented system tables that can change without notice) :

declare @start_date date = '2019-1-22';
declare @end_date date = '2019-1-29';    

with GetDates As  
(  
  select @start_date as TheDate
  union all  
  select dateadd(day, 1, TheDate) from GetDates where TheDate < @end_date
)
select TheDate from GetDates;

Now you can join your data to the Recursive CTE GetDates.

edited Jan 22, 2019 at 10:23

answered Jan 22, 2019 at 10:05

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

Collectives™ on Stack Overflow

select dates between two dates with SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related