How to Generate a Range of Dates in SQL Server

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);

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;

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

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:

Create data for all dates in a date range

You could use two additional helper tables:

• a Dates table that holds all the dates in the desired range, and
• a Zipcodes table that holds the distinct zip codes.

Instead of physical tables, temporary tables and/or table variables, you could also consider using table expressions (subqueries in the FROM-clause) or common table expressions (in a WITH-clause). Common table expressions can often also be recursive, which might be a nice solution for creating a value range with specific start and end values (like your dates range here).

It is already pointed out, that concrete solution proposals heavily depend on the target DBMS. Sadly, it is unspecified here (at the time of writing this answer). Below is a sample implementation for Microsoft SQL Server, using T as the placeholder for your actual table name. It uses two common table expressions: a recursive CTE for the Dates table and a normal CTE for the Zipcodes table.

WITH
  [Dates] AS (
    SELECT CAST('2020-03-01' AS DATE) AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM [Dates]
    WHERE [Date] < '2020-03-15'
  ),
  [ZipCodes] AS (
    SELECT DISTINCT [Zip] FROM T
  )
SELECT D.[Date], Z.[Zip], COALESCE(T.[Revenue], 0) AS [Revenue]
FROM
  [Dates] AS D
  CROSS JOIN [ZipCodes] AS Z
  LEFT JOIN T ON T.[Date] = D.[Date] AND T.[Zip] = Z.[Zip]
ORDER BY Z.[Zip], D.[Date]

Create a row for each date in a range, and add 1 for each day within a date range for a record in SQL

You can join your table to a calendar table containing all the dates you need:

with calendar as
(select cast('2022-01-01' as datetime) as d
union all select dateadd(day, 1, d)
from calendar
where d < '2022-02-01')
select d as "Date", count(*) as NumberOfCustomers 
from calendar inner join table_name
on d between LiveDate and coalesce(ServiceEndDate, '9999-12-31')
group by d;

Fiddle

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