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  DateFROM    dbo.CalendarWHERE   Date >= @MinDateAND     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    TWHERE   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'        ) TWHERE   RowNumber = 1;

You can use this script to find dates between two dates. Reference taken from this Article:

DECLARE @StartDateTime DATETIMEDECLARE @EndDateTime DATETIMESET @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 DateDataFROM DateRangeOPTION (MAXRECURSION 0)GO

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;