Recently a developer came to me seeking my help in generate list dates between date range for a report. Being a .Net developer he said that one solution he had is to do row by row processing either by using while loop or a cursor. We exchanged smiles and he understood what I had in my mind..
I came up with 2 solutions for this problem. One is to use a tally table and another one using a recursive CTE.
Method 1:
DECLARE @dt1 Datetime='2012-08-01'
DECLARE @dt2 Datetime='2012-09-01'
;WITH ctedaterange
AS (SELECT [rn]=Row_number()
OVER(
ORDER BY (SELECT NULL))
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c
CROSS JOIN sys.objects d)
SELECT Dateadd(dd, rn, @dt1)
FROM ctedaterange
WHERE [rn] <= Datediff(dd, @dt1, @dt2)
Method 2:
DECLARE @dt1 Datetime='2012-08-01'
DECLARE @dt2 Datetime='2012-09-01'
;WITH ctedaterange
AS (SELECT [Dates]=@dt1
UNION ALL
SELECT [dates] + 1
FROM ctedaterange
WHERE [dates] + 1<= @dt2)
SELECT [dates]
FROM ctedaterange
OPTION (maxrecursion 0)
Method 1 performs a bit better than method 2 for large result set.
Do let me know if you have a different way to do this.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
In your 2nd method, WHERE clause has to be… Am I correct ? Frown
1 WHERE [dates]+1 <= @dt2
yes it should be … thanks for pointing it out.
Another approach is to use the calendar table. This is more set-oriented than computations
DECLARE @start_date DATE;
DECLARE @end_date DATE;
SET @start_date = ‘2012-08-01’;
SET @end_date = ‘2012-09-01’;
SELECT cal_date
FROM Calendar
WHERE cal_date
BETWEEN @start_date AND @end_date;
Oh yes.. Thanks Joe Celko
As long as @start_date and @end_date are less than 2048 days apart:
DECLARE @start_date [date] = CAST(‘2012-08-01’ as [date])
DECLARE @end_date [date] = CAST(‘2012-09-01’ as [date])
SELECT
DATEADD(day, [v].[number], @start_date)
FROM
[master].[dbo].[spt_values] [v]
WHERE
[v].[type] = ‘P’ AND
DATEADD(day, [v].[number], @start_date) <= @end_date
Nice solutions .., will compile them into a blog .. Thanks everyone …
nice concept by marc,Osama.
I Remember once I stuck into such situation, and I use Second solution provided by Osama.
Thanks, saved me a lot of programing!Sealed
Okay…. Uhm… I need to create a report that actually take a date range and create columns for each weekday in between the range… Any ideas on how to do this?
Hi Drilene,
to get the date part add a column DATEPART(DD,Dateadd(dd, rn, @dt1)) As Day/ Day(Dateadd(dd, rn, @dt1)) As Day to any of the above solution… use pivot to get it as a column name.. it would be more easy if you have a calendar table …
Thanks,
Ahmad
Could this be done with hours instead of days, for example:
ID = 1 Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM then it would return 4 rows like this
ID =1 Instance in Time = 12/20/2016 9:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
ID=1 Instance in Time = 12/20/2016 10:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
ID=1 Instance in Time = 12/20/2016 11:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
ID=1 Instance in Time = 12/20/2016 12:00:00 PM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
any help will be greatly appreciated.
thanks
Cesar