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?
This question is related to
sql
sql-server
cursor
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
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;
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
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 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
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
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
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
...
Source: Stackoverflow.com