Get All Dates Between Two Dates in SQL Server

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;

Get a list of dates between two dates using a function

Try something like this:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

You then use:

SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;

Edited (after the acceptance):

Please note... if you already have a sufficiently large nums table then you should use:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

And you can create such a table using:

CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.

select all dates between two date column in table

Generate a calendar table containing all dates within, e.g. 2018, and then inner join that table to your current table:

DECLARE @todate datetime, @fromdate datetime
SELECT @fromdate='2018-01-01', @todate='2018-12-31'

;WITH calendar (FromDate) AS (
    SELECT @fromdate AS FromDate
    UNION ALL
    SELECT DATEADD(day, 1, FromDate)
    FROM Calendar
    WHERE FromDate < @todate
)

SELECT t1.FromDate
FROM calendar t1
INNER JOIN yourTable t2
    ON t1.FromDate BETWEEN t2.[From] AND t2.[To];

Demo

How to get all dates between two dates in sql server

You Can Try this

Declare @StartDate Date  ='2014-04-01'
               ,@EndDate date ='2014-04-04'

                  (OR)

Declare @StartDate Datetime  ='2014-04-01  00:00:00'
               ,@EndDate datetime ='2014-04-04  12:59:59'

Query:-

Select * from table where date between @Startdate and @EndDate

Get the list of dates between two dates in sql

Easiest way to do this is to either create a numbers table OR use a subquery or CTE to get a list of numbers, then join your vacation table to that, based on start and end date of vacations.

For example,

DECLARE @Vacation TABLE (EmpID INT, VStart DATE, VacEnd DATE, VacType VARCHAR(255));
INSERT @Vacation (EmpID, VStart, VacEnd, VacType) VALUES 
(1, '2017-10-15', '2017-10-19', 'Sick Leave'),
(2, '2017-10-15', '2017-10-16', 'Super Fun Happy Vacation');

SELECT Vacation.EmpID, 
       [Date] = DATEADD(DAY, T.N, Vacation.VStart),
       Vacation.VacType
FROM (
    SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM sys.objects
) AS T
JOIN @Vacation AS Vacation
    ON DATEDIFF(DAY, Vacation.VStart, Vacation.VacEnd) >= T.N;

List all date and time between two dates in SQL Server

Here is an example where I add an ID and the EndTime <> StartTime

Example or dbFiddle

Declare @YourTable Table ([ID] int,[Date_Time] datetime,[Action_c] int,[Action_type] varchar(50))  
Insert Into @YourTable Values 
 (32221,'01-01-2022 13:10:00',1,'Start')
,(32221,'01-03-2022 13:10:00',2,'End')
,(99999,'01-01-2022 13:10:00',1,'Start')    -- Added
,(99999,'01-04-2022 10:00:00',2,'End')      -- Added End Time<>Start Time

;with cte1 as ( 
Select ID
      ,DR1  = min(case when Action_C=1 then Date_Time end)
      ,DR2  = max(case when Action_C=2 then Date_Time end)
      ,nDays= datediff(day, min(case when Action_C=1 then Date_Time end),max(case when Action_C=2 then Date_Time end) )
 From  @YourTable
 Group By ID
), cte2 as (
Select Top 1000 N=-1+Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2
)
Select ID
      ,c.Start_Time 
      ,c.End_Time  
      ,DateDiff   = datediff(minute, c.Start_Time,c.End_Time )
 From  cte1
 Join  cte2 on N< nDays
 Cross Apply ( values ( dateadd(DAY,N,DR1),case when N=nDays-1 then DR2 else dateadd(DAY,N+1,DR1) end ) )C(Start_Time,End_Time)
 Order by ID,Start_Time

Results

ID      Start_Time              End_Time                  DateDiff
32221   2022-01-01 13:10:00.000 2022-01-02 13:10:00.000   1440
32221   2022-01-02 13:10:00.000 2022-01-03 13:10:00.000   1440
99999   2022-01-01 13:10:00.000 2022-01-02 13:10:00.000   1440
99999   2022-01-02 13:10:00.000 2022-01-03 13:10:00.000   1440
99999   2022-01-03 13:10:00.000 2022-01-04 10:00:00.000   1250  -- Note

SQL - SHOW ALL DATES between two dates

Similar to @DhruvJoshi's answer but using a recursive CTE to generate the dates instead:

DECLARE @MinDate DATE = '20170424',
        @MaxDate DATE = '20170430';

WITH allDates AS
(
    SELECT @MinDate AS dates

    UNION ALL

    SELECT DATEADD(DAY, 1, ad.[dates] )
    FROM allDates AS ad
    WHERE ad.[dates] < @MaxDate
)

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT dates
   FROM allDates
) AS T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC

Get DAYs of data between two dates from a date range

try it:

DECLARE @DateSearch1 DATETIME='2017-03-13'
DECLARE @DateSearch2 DATETIME='2017-09-25'
SELECT *,DATEDIFF(DAY,r.dat1,r.dat2) daysOfRange
FROM
(
SELECT  BillID ,
        CASE WHEN FromDate<@DateSearch1 THEN @DateSearch1 ELSE FromDate END AS dat1,
        CASE WHEN ToDate>@DateSearch2 THEN @DateSearch2 ELSE ToDate END AS dat2
FROM    Bills

WHERE   ( FromDate >= @DateSearch1
          AND FromDate <= @DateSearch2
        )
        AND ( ToDate >= @DateSearch1
              AND ToDate <= @DateSearch2
            ))r

Get All Dates Between Two Dates in SQL Server