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
GOHere 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
GOWell, 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)
16 Comments. Leave new
Great..Indeed
Thanks!
Thanks for the script. It would be useful if you could explain what the code is doing.
The code is Common Table Expression which joins its own results to get dates between start and end date.
Thank you, I appreciate it!
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
)
Also, your query will fail if the date range is larger than 100 days.
Fair Point!
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
———————————————————
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.
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.
–add this at the end; and it will not fail even if range is grater than 100
/* by default max recursion is 100 but we can overwrite this by setting it to 0 (infinite) */
option (maxrecursion 0)
Thank u, this helped me a lot.
What if there is a timestamp as well? For example: My start date is 2021-10-14 2:00:00 and end date is 2021-10-16 10:30:00 and I want an output like this:
2021-10-14 2:00:00 2021-10-15 2:00:00
2021-10-15 2:00:00 2021-10-16 2:00:00
2021-10-16 2:00:00 2021-10-16 10:30:00
Your Query also fail when recursion reaches to 100.
Adding “OPTION (MAXRECURSION 0)” In the query, Will allow you to select even 1000 days