SQL SERVER – List All Dates Between Start and End Date

Today we will see a very simple script where we will take advantage of the Common Table Expression (or CTE) and list all dates between two dates (Start and End Date). The script is very simple and lets us see it here first.

List All Dates – Including Start and End Date

DECLARE	@StartDate DATE, @EndDate DATE
SELECT @StartDate = '2021-11-01', @EndDate = '2021-12-01'; 
WITH ListDates(AllDates) AS 
(    SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)
SELECT AllDates
FROM ListDates
GO

Here is the result of the query:

In the script above you can see that we are passing two dates. When you run the query above it gives the result as following where we see the list of all the dates between the start and end date. Remember the way “BETWEEN” works in SQL Server is that it also includes both the endpoints or start and end date.

Excluding Start and End Date

However, if due to any reasons, you do not want to include the start date and end date, you can easily modify the script above as following and it will not include the start and end date in the results.

DECLARE	@StartDate DATE, @EndDate DATE
SELECT @StartDate = '2021-11-01', @EndDate = '2021-12-01'; 
WITH ListDates(AllDates) AS 
(    SELECT DATEADD(DAY,1,@StartDate) AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < DATEADD(DAY,-1,@EndDate))
SELECT AllDates
FROM ListDates
GO

Well, that is for today. A very simple blog post about the necessary script. Please let me know if you have any questions on this topic or want me to write any other script. You can also subscribe to my YouTube Channel for SQL videos related to Performance Tuning.

Reference: Pinal Dave (https://blog.sqlauthority.com)