Generating Dates Between Two Dates

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

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;

How to get all the dates between two dates?

An alternative solution to your problem is using pandas

import pandas as pd
pd.date_range(start=start_date,end=end_date)

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

postgresql generating a list of dates between two dates fields

Use generate_series()

select t.id, t.name, t.g.dt::date as start_end
from the_table t
  cross join generate_series(t.date_start, t.date_end, interval '1 day') as g(dt)
order by t.id, g.dt;

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

---

Related Topics