26

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?

Regards,

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dnakk Jam
  • 351
  • 1
  • 3
  • 10

5 Answers5

49

You can use a numbers table:

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20150528'
SET @Date2 = '20150531'

SELECT DATEADD(DAY,number+1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@Date1) < @Date2

Results:

╔════════════╗
║    Date    ║
╠════════════╣
║ 2015-05-29 ║
║ 2015-05-30 ║
╚════════════╝
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • 3
    I wouldn't do that. what if I don't have access to `master`? – Luis LL Jul 08 '13 at 15:23
  • 1
    @LuisLL That's just a quick example of a numbers table, op could use any other one, or a calendar table. It is typically faster than a recursive CTE (though I like to use them, I know they bring performance issues) – Lamak Jul 08 '13 at 15:26
  • 2
    Has limitation if you need to get a long date range... like 15-20 years. Since it is using table master..spt_values – gmsi Sep 29 '17 at 14:53
  • Not bulletproof since the values range from `spt_values` is limited to 0-2047. – caiosm1005 Jun 25 '19 at 23:33
  • It is the best solution for most people, it's short and generates less noise, even if it is not the most general. That being said, one might wonder why generating a list of numbers/dates is so difficult with SQL... – Florian Fasmeyer Feb 24 '21 at 14:40
34

Use this,

DECLARE @start_date DATETIME = '2015-02-12 00:00:00.000';
DECLARE @end_date DATETIME = '2015-02-13 00:00:00.000';

WITH    AllDays
          AS ( SELECT   @start_date AS [Date], 1 AS [level]
               UNION ALL
               SELECT   DATEADD(DAY, 1, [Date]), [level] + 1
               FROM     AllDays
               WHERE    [Date] < @end_date )
     SELECT [Date], [level]
     FROM   AllDays OPTION (MAXRECURSION 0)

pass the @start_date and @end_date as SP parameters.

Result:

Date                    level
----------------------- -----------
2015-02-12 00:00:00.000 1
2015-02-13 00:00:00.000 2

(2 row(s) affected)
SAM
  • 825
  • 1
  • 7
  • 15
  • It works perfectly but I cant grasp an understanding on how it works, do you have a link for reference? Thanks for your help. – RaRdEvA Aug 30 '22 at 01:32
20

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

SQL Fiddle with demo.

Or better still create a calendar table and just select from that.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
1

I made a calendar using:

http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

then a Store procedure passing two dates and thats all:

USE DB_NAME;
GO

CREATE PROCEDURE [dbo].[USP_LISTAR_RANGO_FECHAS]
@FEC_INICIO date,
@FEC_FIN date
AS
Select Date from CALENDARIO where Date BETWEEN @FEC_INICIO AND @FEC_FIN;
julio castillo
  • 11
  • 1
1

You can create a stored procedure passing 2 dates

CREATE PROCEDURE SELECTALLDATES
(
@StartDate as date,
@EndDate as date
)
AS
Declare @Current as date = DATEADD(DD, 1, @BeginDate);

Create table #tmpDates
(displayDate date)

WHILE @Current < @EndDate
BEGIN
insert into #tmpDates
VALUES(@Current);
set @Current = DATEADD(DD, 1, @Current) -- add 1 to current day
END

Select * 
from #tmpDates

drop table #tmpDates
Dan Horton
  • 105
  • 1
  • 5