Generate Days from Date Range

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

Python generating a list of dates between two dates

You can use pandas.date_range() for this:

import pandas
pandas.date_range(sdate,edate-timedelta(days=1),freq='d')

DatetimeIndex(['2019-03-22', '2019-03-23', '2019-03-24', '2019-03-25',
           '2019-03-26', '2019-03-27', '2019-03-28', '2019-03-29',
           '2019-03-30', '2019-03-31', '2019-04-01', '2019-04-02',
           '2019-04-03', '2019-04-04', '2019-04-05', '2019-04-06',
           '2019-04-07', '2019-04-08'],
          dtype='datetime64[ns]', freq='D')

Generate days from date range sql

You could add a limit clause (and an order by)

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.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
) a 
order by 1 desc 
limit 365

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.

Creating a range of dates in Python

Marginally better...

base = datetime.datetime.today()
date_list = [base - datetime.timedelta(days=x) for x in range(numdays)]

Javascript - get array of dates between 2 dates

function (startDate, endDate, addFn, interval) {

 addFn = addFn || Date.prototype.addDays;
 interval = interval || 1;

 var retVal = [];
 var current = new Date(startDate);

 while (current <= endDate) {
  retVal.push(new Date(current));
  current = addFn.call(current, interval);
 }

 return retVal;

}

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;

Generating a series of dates

I think having a calendar table is a good idea; you can gain a lot of reporting and query functionality, especially when filling sparse data ranges.

I found this article with what seems to be a good example.

Pandas: Generate date intervals between two dates with yearly reset

You could loop creating a date_range up to the start of the next year for each year, appending them until you hit the end date.

import pandas as pd
from datetime import date

def date_range_with_resets(start, end, freq):
  start = date.fromisoformat(start)
  end = date.fromisoformat(end)
  result = pd.date_range(start=start, end=start, freq=freq) # initialize result with just start date
  next_year_start = start.replace(year=start.year+1, month=1, day=1)
  while next_year_start < end:
    result = result.append(pd.date_range(start=start, end=next_year_start, freq=freq))
    start = next_year_start
    next_year_start = next_year_start.replace(year=next_year_start.year+1)
  result = result.append(pd.date_range(start=start, end=end, freq=freq))
  return result[1:] # remove duplicate start date

start = '2018-12-01'
end = '2019-01-31'
date_range_with_resets(start, end, freq='8D')

Edit:
Here's a simpler way without using datetime. Create a date_range of years between start and end, then loop through those.

def date_range_with_resets(start, end, freq):
  years = pd.date_range(start=start, end=end, freq='YS') # YS=year start
  if len(years) == 0:
    return pd.date_range(start=start, end=end, freq=freq)
  result = pd.date_range(start=start, end=years[0], freq=freq)
  for i in range(0, len(years) - 1):
    result = result.append(pd.date_range(start=years[i], end=years[i+1], freq=freq))
  result = result.append(pd.date_range(start=years[-1], end=end, freq=freq))
  return result

Generate date range in between 2 dates

You can use a recursive cte if you put it in a table valued function

CREATE FUNCTION FnDateRange
(   
    @startDate date,
    @endDate date
)
RETURNS @DateRange Table
(myDate date)
AS
begin
    with Dates_rte as
    (
        select @startDate myDate
        union all
        select cast(dateadd(day,1,myDate) as date)
        from Dates_rte
        where cast(dateadd(day,1,myDate) as date) <= @endDate
    )
    insert into @DateRange
    select * from Dates_rte option (maxrecursion 0)

    return
end
GO

select * from fnDateRange('2017-07-01','2017-07-06')