Get all dates between two dates in SQL Server

How to get all the dates between two dates?

I have a variable @MAXDATE which is storing the maximum date from the table. Now I want to get the all dates between @Maxdate and GETDATE() and want to store these dates in a cursor.

So far I have done as follows:

;with GetDates As  
(  
    select DATEADD(day,1,@maxDate) as TheDate
    UNION ALL  
    select DATEADD(day,1, TheDate) from GetDates  
    where TheDate < GETDATE()  
)  

This is working perfectly but when I am trying to store these values in a cursor

SET @DateCurSor = CURSOR FOR
				SELECT TheDate
				FROM GetDates

Compilation Error

> Incorrect syntax near the keyword 'SET'.

How to solve this?

My first suggestion would be use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

DECLARE @MinDate DATE = '20140101',
		@MaxDate DATE = '20140106';

SELECT	Date
FROM	dbo.Calendar
WHERE	Date >= @MinDate
AND		Date < @MaxDate;

If you don't want to, or can't create a calendar table you can still do this on the fly without a recursive CTE:

DECLARE @MinDate DATE = '20140101',
		@MaxDate DATE = '20140106';

SELECT	TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
		Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM	sys.all_objects a
		CROSS JOIN sys.all_objects b;

For further reading on this see:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

With regard to then using this sequence of dates in a cursor, I would really recommend you find another way. There is usually a set based alternative that will perform much better.

So with your data:

  date   | it_cd | qty 
24-04-14 |  i-1  | 10 
26-04-14 |  i-1  | 20

To get the quantity on 28-04-2014 (which I gather is your requirement), you don't actually need any of the above, you can simply use:

SELECT	TOP 1 date, it_cd, qty 
FROM	T
WHERE	it_cd = 'i-1'
AND		Date <= '20140428'
ORDER BY Date DESC;

If you don't want it for a particular item:

SELECT	date, it_cd, qty 
FROM	(	SELECT	date, 
					it_cd, 
					qty, 
					RowNumber = ROW_NUMBER() OVER(PARTITION BY ic_id 
													ORDER BY date DESC)
			FROM	T
			WHERE	Date  <= '20140428'
		) T
WHERE	RowNumber = 1;

You can use this script to find dates between two dates. Reference taken from this Article:

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
 
SET @StartDateTime = '2015-01-01'
SET @EndDateTime = '2015-01-12';
 
WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData < @EndDateTime
)
SELECT DateData
FROM DateRange
OPTION (MAXRECURSION 0)
GO

Just saying...here is a more simple approach to this:

declare @sdate date = '2017-06-25'
	, @edate date = '2017-07-24';

with dates_CTE (date) as (
	select @sdate 
	Union ALL
	select DATEADD(day, 1, date)
	from dates_CTE
	where date < @edate
)
select *
from dates_CTE;

Easily create a Table Value Function that will return a table with all dates. Input dates as string You can customize the date in the the format you like '01/01/2017' or '01-01-2017' in string formats (103,126 ...)

Try this

CREATE FUNCTION [dbo].[DateRange_To_Table] ( @minDate_Str NVARCHAR(30), @maxDate_Str NVARCHAR(30))

RETURNS  @Result TABLE(DateString NVARCHAR(30) NOT NULL, DateNameString NVARCHAR(30) NOT NULL)

AS

begin

    DECLARE @minDate DATETIME, @maxDate DATETIME
	SET @minDate = CONVERT(Datetime, @minDate_Str,103)
	SET @maxDate = CONVERT(Datetime, @maxDate_Str,103)


	INSERT INTO @Result(DateString, DateNameString )
	SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30),DATENAME(dw,@minDate))



	WHILE @maxDate > @minDate
	BEGIN
		SET @minDate = (SELECT DATEADD(dd,1,@minDate))
		INSERT INTO @Result(DateString, DateNameString )
		SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30),DATENAME(dw,@minDate))
	END


   

    return

end   

To execute the function do this:

SELECT * FROM dbo.DateRange_To_Table ('01/01/2017','31/01/2017')

The output will be

01/01/2017	Sunday
02/01/2017	Monday
03/01/2017	Tuesday
04/01/2017	Wednesday
05/01/2017	Thursday
06/01/2017	Friday
07/01/2017	Saturday
08/01/2017	Sunday
09/01/2017	Monday
10/01/2017	Tuesday
11/01/2017	Wednesday
12/01/2017	Thursday
13/01/2017	Friday
14/01/2017	Saturday
15/01/2017	Sunday
16/01/2017	Monday
17/01/2017	Tuesday
18/01/2017	Wednesday
19/01/2017	Thursday
20/01/2017	Friday
21/01/2017	Saturday
22/01/2017	Sunday
23/01/2017	Monday
24/01/2017	Tuesday
25/01/2017	Wednesday
26/01/2017	Thursday
27/01/2017	Friday
28/01/2017	Saturday
29/01/2017	Sunday
30/01/2017	Monday
31/01/2017	Tuesday

This can be considered as bit tricky way as in my situation, I can't use a CTE table, so decided to join with sys.all_objects and then created row numbers and added that to start date till it reached the end date.

See the code below where I generated all dates in Jul 2018. Replace hard coded dates with your own variables (tested in SQL Server 2016):

select top (datediff(dd, '2018-06-30', '2018-07-31')) ROW_NUMBER() 
over(order by a.name) as SiNo, 
Dateadd(dd, ROW_NUMBER() over(order by a.name) , '2018-06-30') as Dt from sys.all_objects a

You can try this:

	SET LANGUAGE SPANISH

DECLARE @startDate DATE = GETDATE() -- Your start date
DECLARE @endDate DATE = DATEADD(MONTH, 16, GETDATE()) -- Your end date
DECLARE @years INT = YEAR(@endDate) - YEAR(@startDate)

CREATE TABLE #TMP_YEARS (
	[year] INT
)

-- Get all posible years between the start and end date
WHILE @years >= 0
BEGIN
	INSERT INTO #TMP_YEARS
	([year])
	SELECT YEAR(@startDate) + @years

	SET @years = @years - 1
END

;WITH [days]([day]) AS -- Posible days at a month
(
	SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL -- days lower than 10
	SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL -- days lower than 20
	SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL -- days lower than 30
	SELECT 30 UNION ALL SELECT 31 -- days higher 30
),
[months]([month]) AS -- All months at a year
(
	SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) as [date]
  FROM #TMP_YEARS a
 CROSS JOIN [months] n -- Join all years with all months
 INNER JOIN [days] d on DAY(EOMONTH(CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + CONVERT(VARCHAR, DAY(EOMONTH(CAST(CONVERT(VARCHAR, a.[year]) + '-' + CONVERT(varchar, n.[month]) + '-15' AS DATE)))))) >= d.[day] AND -- The number of the day can't be higher than the last day of the current month and the current year
					  CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) <= ISNULL(@endDate, GETDATE()) AND -- The current date can't be higher than the end date
					  CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) >= ISNULL(@startDate, GETDATE()) -- The current date should be higher than the start date
 ORDER BY a.[year] ASC, n.[month] ASC, d.[day] ASC

The output will be something like this, you can format the date as you like:

2019-01-24
2019-01-25
2019-01-26
2019-01-27
2019-01-28
2019-01-29
2019-01-30
2019-01-31
2019-02-01
2019-02-02
2019-02-03
2019-02-04
2019-02-05
2019-02-06
2019-02-07
2019-02-08
2019-02-09
...

create procedure [dbo].[p_display_dates](@startdate datetime,@enddate datetime)
as
begin
	declare @mxdate datetime
	declare @indate datetime
	create table #daterange (dater datetime)
	insert into #daterange values (@startdate)
	set @mxdate = (select MAX(dater) from #daterange)
	while @mxdate < @enddate
		begin
			set @indate = dateadd(day,1,@mxdate)
			insert into #daterange values (@indate)
			set @mxdate = (select MAX(dater) from #daterange)
		end
	select * from #daterange
end

I listed dates of 2 Weeks later. You can use variable @period OR function datediff(dd, @date_start, @date_end)

declare @period INT, @date_start datetime, @date_end datetime, @i int;

set @period = 14
set @date_start = convert(date,DATEADD(D, -@period, curent_timestamp))
set @date_end = convert(date,current_timestamp)
set @i = 1

create table #datesList(dts datetime)
insert into #datesList values (@date_start)
while @i <= @period
	Begin
		insert into #datesList values (dateadd(d,@i,@date_start))
		set @i = @i + 1
	end
select cast(dts as DATE) from #datesList
Drop Table #datesList

This is the method that I would use.

DECLARE 
	@DateFrom DATETIME = GETDATE(),
	@DateTo DATETIME = DATEADD(HOUR, -1, GETDATE() + 2); -- Add 2 days and minus one hour


-- Dates spaced a day apart 

WITH MyDates (MyDate)
AS (
	SELECT @DateFrom
	UNION ALL
	SELECT DATEADD(DAY, 1, MyDate)
	FROM MyDates
	WHERE MyDate < @DateTo
   )

SELECT 
	MyDates.MyDate
	, CONVERT(DATE, MyDates.MyDate) AS [MyDate in DATE format]
FROM 
	MyDates;

Here is a similar example, but this time the dates are spaced one hour apart to further aid understanding of how the query works:

-- Alternative example with dates spaced an hour apart

WITH MyDates (MyDate)
AS (SELECT @DateFrom
	UNION ALL
	SELECT DATEADD(HOUR, 1, MyDate)
	FROM MyDates
	WHERE MyDate < @DateTo
   )

SELECT 
	MyDates.MyDate
FROM 
	MyDates;

As you can see, the query is fast, accurate and versatile.

You can use SQL Server recursive CTE

DECLARE 
	@MinDate DATE = '2020-01-01',
	@MaxDate DATE = '2020-02-01';

WITH Dates(day) AS 
(
	SELECT CAST(@MinDate as Date) as day
	UNION ALL
	SELECT CAST(DATEADD(day, 1, day) as Date) as day
	FROM Dates
	WHERE CAST(DATEADD(day, 1, day) as Date) < @MaxDate
)
SELECT* FROM dates;

declare @start_dt as date = '1/1/2021';		-- Date from which the calendar table will be created.
declare @end_dt as date = '1/1/2022';		-- Calendar table will be created up to this date (not including).

declare @dates as table (
 date_id date primary key,
 date_year smallint,
 date_month tinyint,
 date_day tinyint,
 weekday_id tinyint,
 weekday_nm varchar(10),
 month_nm varchar(10),
 day_of_year smallint,
 quarter_id tinyint,
 first_day_of_month date,
 last_day_of_month date,
 start_dts datetime,
 end_dts datetime
)

while @start_dt < @end_dt
begin
	insert into @dates(
		date_id, date_year, date_month, date_day, 
		weekday_id, weekday_nm, month_nm, day_of_year, quarter_id, 
		first_day_of_month, last_day_of_month, 
		start_dts, end_dts
	)	
	values(
		@start_dt, year(@start_dt), month(@start_dt), day(@start_dt), 
		datepart(weekday, @start_dt), datename(weekday, @start_dt), datename(month, @start_dt), datepart(dayofyear, @start_dt), datepart(quarter, @start_dt),
		dateadd(day,-(day(@start_dt)-1),@start_dt), dateadd(day,-(day(dateadd(month,1,@start_dt))),dateadd(month,1,@start_dt)), 
		cast(@start_dt as datetime), dateadd(second,-1,cast(dateadd(day, 1, @start_dt) as datetime))
	)
	set @start_dt = dateadd(day, 1, @start_dt)
end


-- sample of the data

select 
top 50 * 
--into master.dbo.DimDate
from @dates d
order by date_id

DECLARE @FirstDate DATE = '2018-01-01'
DECLARE @LastDate Date = '2018-12-31'
DECLARE @tbl TABLE(ID INT IDENTITY(1,1) PRIMARY KEY,CurrDate date)
INSERT @tbl VALUES( @FirstDate)
WHILE @FirstDate < @LastDate
BEGIN
SET @FirstDate = DATEADD( day,1, @FirstDate)
INSERT @tbl VALUES( @FirstDate)
END
INSERT @tbl VALUES( @LastDate) 

SELECT * FROM @tbl