CODEWITHSUNDEEP
sqlsql-server
navid karampour
navid karampour

Reputation: 93

select dates between two dates with SQL Server

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

Upvotes: 0

Views: 350

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6455

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.

Upvotes: 0

PSK
PSK

Reputation: 17943

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.

Upvotes: 3

Related Questions