Generate a Range of Dates Using SQL

Generate Dates between date ranges

Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.

EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here's some code to create and populate a table.

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.

DECLARE @StartDate DATE = '20110901'
      , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

Performant, and no recursion.

How to generate a range of dates in SQL Server

I would argue that for this specific purpose the below query is about as efficient as using a dedicated lookup table.

DECLARE @start DATE, @end DATE;
SELECT @start = '20110714', @end = '20110717';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)
SELECT 'Bob', DATEADD(DAY, n-1, @start)
FROM n;

Results:

Bob     2011-07-14
Bob     2011-07-15
Bob     2011-07-16
Bob     2011-07-17

---

Presumably you'll need this as a set, not for a single member, so here is a way to adapt this technique:

DECLARE @t TABLE
(
    Member NVARCHAR(32), 
    RegistrationDate DATE, 
    CheckoutDate DATE
);

INSERT @t SELECT N'Bob', '20110714', '20110717'
UNION ALL SELECT N'Sam', '20110712', '20110715'
UNION ALL SELECT N'Jim', '20110716', '20110719';

;WITH [range](d,s) AS 
(
  SELECT DATEDIFF(DAY, MIN(RegistrationDate), MAX(CheckoutDate))+1,
    MIN(RegistrationDate)
    FROM @t -- WHERE ?
),
n(d) AS
(
  SELECT DATEADD(DAY, n-1, (SELECT MIN(s) FROM [range]))
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects) AS s(n)
  WHERE n <= (SELECT MAX(d) FROM [range])
)
SELECT t.Member, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;
----------^^^^^^^ not many cases where I'd advocate between!

Results:

Member    d
--------  ----------
Bob       2011-07-14
Bob       2011-07-15
Bob       2011-07-16
Bob       2011-07-17
Sam       2011-07-12
Sam       2011-07-13
Sam       2011-07-14
Sam       2011-07-15
Jim       2011-07-16
Jim       2011-07-17
Jim       2011-07-18
Jim       2011-07-19

As @Dems pointed out, this could be simplified to:

;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT t.Member, d = DATEADD(DAY, natural.val, t.RegistrationDate) 
  FROM @t AS t INNER JOIN natural 
  ON natural.val <= DATEDIFF(DAY, t.RegistrationDate, t.CheckoutDate);

generate days from date range

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all 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) as a
    cross join (select 0 as a union all 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) as b
    cross join (select 0 as a union all 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) as c
    cross join (select 0 as a union all 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

Get all dates between two dates in SQL Server

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;

How to Auto generate dates between date range using SQL Query?

Here is how to accomplish this by using a tally table to create a calendar table:

declare @source table
(
    user_id int not null primary key clustered,
    from_date date not null,
    to_date date not null
);

insert into @source
values
(1, '02/20/2019', '02/23/2019'),
(2, '02/22/2019', '02/28/2019'),
(3, '03/01/2019', '03/05/2019');

with
rows as
(
    select top 1000
    n = 1
    from sys.messages
),
tally as
(
    select n = row_number() over(order by (select null)) - 1
    from rows
),
calendar as
(
    select
    date = dateadd(dd, n, (select min(from_date) from @source))
    from tally
)
select
s.user_id,
c.date
from @source s
cross join calendar c
where c.date between s.from_date and s.to_date;

Result set:

generating range of dates table with rownum

You may use recursive CTE for this purpose.

You only need to pass the start date and the stop date in the anchor select of the
recursive CTE

Example

with cal (dt, stop) as (
select date'2022-01-01', date'2022-01-05' from dual
union all
select dt+1, stop from cal
where dt< stop)
select dt from cal;

DT                 
-------------------
01.01.2022 00:00:00
02.01.2022 00:00:00
03.01.2022 00:00:00
04.01.2022 00:00:00
05.01.2022 00:00:00

Generate a range of records depending on from-to dates

Row generator it is, but not as you did it; most probably you're missing lines #11 - 16 in my query (or their alternative).

SQL> with test (item, date_from, date_to) as
  2    -- sample data
  3    (select 'A', date '2018-01-03', date '2018-03-16' from dual union all
  4     select 'B', date '2021-05-25', date '2021-11-10' from dual
  5    )
  6  -- query that returns desired result
  7  select item,
  8         extract(month from (add_months(date_from, column_value - 1))) month,
  9         extract(year  from (add_months(date_from, column_value - 1))) year
 10  from test cross join
 11    table(cast(multiset
 12      (select level
 13       from dual
 14       connect by level <=
 15         months_between(trunc(least(sysdate, date_to), 'mm'), trunc(date_from, 'mm')) + 1
 16      ) as sys.odcinumberlist))
 17  order by item, year, month;

ITEM       MONTH       YEAR
----- ---------- ----------
A              1       2018
A              2       2018
A              3       2018
B              5       2021
B              6       2021
B              7       2021
B              8       2021

7 rows selected.

SQL>

Create a dynamic date range in SQL

In SQL Server, you can use eomonth():

select dateadd(day, 1, eomonth(getdate(), -2)) as start_date,
       eomonth(getdate(), -1) as end_date

Here is a db<>fiddle.

---

Related Topics