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)