How to list all dates between two dates

Question

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

User · Answer

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   lt   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

User · Answer

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  lt   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

User · Answer

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   lt   Date2   Results    ------------        Date         ------------      2015-05-29       2015-05-30     ------------

User · Answer

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   lt   endDate   union all   select dateadd dd  1  dt    from dateRange   where dateadd dd  1  dt   lt   endDate   select   from dateRange   SQL Fiddle with demo   Or better still create a calendar table and just select from that

User · Answer

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

[sql-server] How to list all dates between two dates

Examples related to sql-server

Examples related to stored-procedures