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?
Solution 1:
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;
Solution 2:
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
Solution 3:
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;