SQL Server: Return All Dates in a Range

This function will return a list of all dates between a start date and an end date.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        bryan valencia
-- Create date: 11/20/2018
-- Description:    returns the dates between start and end (inclusive).
-- =============================================
CREATE FUNCTION DaysBetween(@startdate DATE, @enddate DATE)
RETURNS @calendar TABLE
(
    calendarday DATE PRIMARY KEY
)
AS
BEGIN
        -- Fill the table variable with the rows for your result set

    WITH calendar AS
    (
      SELECT CAST(@startdate AS DATETIME) DateValue
      UNION ALL
      SELECT  DateValue + 1
      FROM    calendar  
      WHERE   DateValue + 1 <= @enddate
    )

    insert into @calendar(calendarday)
    (
    SELECT  cast(DateValue as Date) calendarday
    FROM    calendar
    )
    OPTION (MAXRECURSION 0)
   

    RETURN
END
GO


To call it: SELECT * FROM [dbo].[DaysBetween] ('01 jan 2018', '01 feb 2018')



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.