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.

SQL SERVER - List All Dates Between Start and End Date listalldates-800x643

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:

SQL SERVER - List All Dates Between Start and End Date ListAllDates

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)

, , ,
Previous Post
SQL SERVER – Remove Unused Cache MARK_IN_USE_FOR_REMOVAL
Next Post
SQL SERVER – Move a Table From One Schema to Another Schema

Related Posts

11 Comments. Leave new

  • Great..Indeed

    Reply
  • Thanks for the script. It would be useful if you could explain what the code is doing.

    Reply
    • The code is Common Table Expression which joins its own results to get dates between start and end date.

      Reply
  • Barbara Cooper
    January 13, 2021 6:55 pm

    Thank you, I appreciate it!

    Reply
  • I’ve also used something like this:

    DECLARE @s DATE = ‘20201001’, @e DATE = ‘20211001’;

    WITH CTE ( Number) as
    (
    SELECT 1
    UNION ALL
    SELECT Number + 1
    FROM CTE
    WHERE Number < 1000
    ),
    Calendar as
    (
    SELECT TOP (DATEDIFF(DAY, @s, @e))
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, @s) as CDate
    FROM CTE
    )

    Reply
  • Also, your query will fail if the date range is larger than 100 days.

    Reply
  • Hi, I have a kind of similar query here to identify which user was available between these timestamps.

    ——————————————————–
    Resource StartTime EndTime
    John 1/20/2021 17:30 1/21/2021 2:00
    Tom 1/20/2021 17:30 1/21/2021 2:00
    Blair 1/20/2021 22:00 1/21/2021 6:30
    Jack 1/20/2021 22:00 1/21/2021 6:30
    ———————————————————

    Reply
  • Sir , how to create or alter stored procedure based on some certain condition. like if database name=DB_123 –create stored procedure else if database name=db_345 alter stored procedure.

    I tried using execute(‘create procedurec….’) , but i have so many procedures each procedure having 1000 lines. to place all those procedure in this execute statement it will take so much time, is there any simplest way to create or alter stored procedure based on some condition.

    Thanks in Advance.

    Reply
  • Dude, you’ve helped me countless of times. Whenever people ask me for SQL stuff, I tell them ‘Ask Pinal Dave’ haha. Thanks. Cheers from Paris.

    Reply

Leave a Reply

Menu