Get all dates between two dates in SQL Server

Question

How can I 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?

***WHY on earth*** do you want a **cursor** ?!? You should try to **avoid cursors** as much as you can! — marc_s, Apr 25 '14 at 10:31
***W H Y ? ? ?*** I'm 99% sure you don't **have to** use a cursor! And it would be better if you didn't use a cursor! — marc_s, Apr 25 '14 at 10:34
I have a table which is containing date,item code and quantity. Suppose the data of the table is as follows: date ||it_cd||qty 24-04-14||i-1 ||10 26-04-14||i-1 ||20 Now how will I get the qty on 28-04-2014 — user3193557, Apr 25 '14 at 10:38
If you want to show a table, please *edit* your question and add it. There's no formatting in the comments. — Damien_The_Unbeliever, Apr 25 '14 at 10:40

score 114 · Accepted Answer · edited Nov 04 '19 at 14:41

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:

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;

I'm not sure the cross join is necessary. Seems to be irrelevant when executing the query. — pim, Aug 29 '16 at 23:16
@PimBrouwers The necessity of the cross join is in how many rows are required. If less dates are required than the number of objects in `sys.all_objects`, then it isn't required, but if the date range were to span 20 years, then the cross join would be needed. Using `TOP (Days needed)` means that there is little or no overhead from the cross join when less rows are needed, so there is no harm it leaving it in. — GarethD, Aug 30 '16 at 08:00
Awesome! Thanks for explaining that. Much appreciated. Fantastic query overall. — pim, Aug 30 '16 at 12:39
Here is another good article: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — Jose, Sep 18 '18 at 23:45

score 35 · Answer 2 · answered Oct 28 '15 at 06:23

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

score 16 · Answer 3 · edited Jul 12 '20 at 22:23

16

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;

edited Jul 12 '20 at 22:23

Dale K

25,246
15
42
71

answered Jul 25 '17 at 15:04

GoldBishop

2,820
4
47
82

1

For those that use this....time and experience have left their mark with me....CTE's are great for pseudo-looping, AS LONG AS the resulting set is small (in this example, less than 30 days; I would advise keeping it less than 90 days)....anything greater than 90 days, you should probably build a lookup table with some predictable date patterns....refresh your self on the logic for Leap Year identification ;) – GoldBishop Aug 25 '21 at 02:47

score 6 · Answer 4 · answered Jun 21 '17 at 09:17

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

score 2 · Answer 5 · edited Jul 12 '20 at 22:22

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

score 1 · Answer 6 · answered Jan 24 '19 at 11:42

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:

score 1 · Answer 7 · answered Dec 12 '20 at 16:22

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;

score 0 · Answer 8 · edited Nov 25 '14 at 06:27

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

score 0 · Answer 9 · answered Aug 08 '17 at 12:33

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

score 0 · Answer 10 · answered Nov 29 '17 at 11:29

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.

score 0 · Answer 11 · answered May 11 '22 at 12:06

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

score 0 · Answer 12 · answered Aug 28 '23 at 15:00

If you are using SQL Server 2022+ or Azure SQL database and don't have a suitable calendar table available (or are using this to create one!) then the simplest way as of today is probably using GENERATE_SERIES

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

SELECT DATEADD(DAY, value, @MinDate) AS [Date]
FROM GENERATE_SERIES(0, DATEDIFF(DAY ,@MinDate, @MaxDate))

Rakesh Dara · Answer 13 · 2023-08-02T04:55:36.973

-1

If you use Postgresql, this approach will work

SELECT date_trunc('day', dd):: DATE AS date FROM GENERATE_SERIES ('2023-08-03'::timestamp,'2023-08-04'::timestamp, '1 day'::interval) dd

edited Aug 02 '23 at 04:55

answered Aug 02 '23 at 04:49

Rakesh Dara

27
4

Please [edit] your answer to provide more information as to how the query you've provided works, what you have changed, etc. Code-only answers might solve the problem of the original asker but they don't help future readers understand the solution. – LW001 Aug 06 '23 at 15:44

score -2 · Answer 14 · edited Dec 25 '18 at 12:16

-2

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

edited Dec 25 '18 at 12:16

Corion

3,855
1
17
27

answered Dec 25 '18 at 11:56

Hari Kumar

1

Get all dates between two dates in SQL Server

14 Answers14

Linked

Related