SQL Query to Select Dates Between Two Dates

SQL query to select dates between two dates

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

SQL query to select dates between two dates and exclude dates between two dates

If you want to select rows having dates between two date values and which are not in an other table, first select the rows using Between .. and in a Where clause and omit the dates which are part of other table using Not Exists.

Query

select * from [dbo].[#temp1] as [t1]
where cast([workDate] as date) between '2018-09-01' and '2018-12-01'
and not exists(
    select 1 from [dbo].[#temp2] as [t2]
    where [t1].[workDate] = [t2].[workDate]
);

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

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 SELECT between two dates in SQL Server

My preferred method is:

SELECT COUNT(*) AS score
FROM requests
WHERE date_em >= '2019-04-01' AND
      date_em < '2019-05-01'

This handles the time component and will make use of an index.

The following also works well in SQL Server:

SELECT COUNT(*) AS score
FROM requests
WHERE CONVERT(date, date_em) BETWEEN '2019-04-01' AND '2019-04-30'

In SQL Server, this will also use an index (if available). However, normally functions on columns preclude the use of an index, so I'm more hesitant about this approach.

Get a list of dates between two dates using a function

this few lines are the simple answer for this question in sql server.

WITH mycte AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 < '2021-12-31'
)

SELECT  DateValue
FROM    mycte
OPTION (MAXRECURSION 0)

select dates between two dates with SQL Server

If you don’t have calendar table available , you can try like following query using master..[spt_values] to generate the missing dates.

;WITH cte 
     AS (SELECT ( Row_number() 
                    OVER ( 
                      ORDER BY (SELECT NULL)) ) - 1 RN 
         FROM   master..[spt_values] T1) 
SELECT id, 
       state, Dateadd(day, rn, start_date)     AS start_date, 
       Dateadd(day, rn + 1, start_date) AS end_date 
FROM   <Table_Name> t1 
       INNER JOIN cte T2 
               ON Dateadd(day, rn, start_date) < t1.end_date 

Note: Replace with appropriate table name.

Count between two dates for each row

Try the statement below.

Note, that you should use RANGE and not ROWS inside the OVER clause, if there is a chance, that there is no row for some month in your table as in the example. The expression used in ORDER BY returns continuous enumeration for months, which is necessary for our algorithm with RANGE.

If there are no gaps in months, then you may use ORDER BY YEARMONTH ROWS BETWEEN 11 PRECEDING AND CURRENT ROW instead.

SELECT 
  YEARMONTH
, SUM (SALES) OVER 
(
ORDER BY YEARMONTH / 100 * 12 + MOD (YEARMONTH, 100) 
RANGE BETWEEN 11 PRECEDING AND CURRENT ROW 
) AS SALES
FROM 
(
VALUES
  (202110, 1)
--, (202109, 1)
, (202108, 1)
, (202107, 1)
, (202106, 1)
, (202105, 1)
, (202104, 1)
, (202103, 1)
, (202102, 1)
, (202101, 1)
, (202012, 1)
, (202011, 1)
, (202010, 1)
, (202009, 1)
, (202008, 1)
) T (YEARMONTH, SALES)
ORDER BY YEARMONTH DESC

---