HOW TO LIST ALL DATES BETWEEN TWO DATES

Kate Smith

Total Post:24

Points:168
Posted by  Kate Smith
 1116  View(s)
Ratings:
Rate this:
Hi Expert! 

I would like list dates between two date in a SQL Server stored procedure.

For example:

Date1: 2015-05-28
Date2: 2015-05-31

Results :

2015-05-29
2015-05-30

How to calculate all dates between two given dates?

Thanks in advance. 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: How to list all dates between two dates

    Hi Kate!


    Create a stored procedure that does something like the following:

    declare @startDate date;
    declare @endDate date;

    select @startDate = '20150528';
    select @endDate = '20150531';

    with dateRange as
    (
      select dt = dateadd(dd, 1, @startDate)
      where dateadd(dd, 1, @startDate) < @endDate
      union all
      select dateadd(dd, 1, dt)
      from dateRange
      where dateadd(dd, 1, dt) < @endDate
    )
    select *
    from dateRange

    I hope it helpful for you.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!