SQL Select Between 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 data between two dates and times

You need single quotes around the values:

SELECT * FROM  [table] 
WHERE [time] >='2014-04-08 23:53:00.000' AND [time] <= '2014-04-08 23:58:00.000'

While I'm here, you are mistaken about how the data is formatted. The yyy-MM-dd HH:mm:ss.fff format is just a convenience shown for you by visual studio, management studio, or your query tool. Datetime columns are actually stored in a binary format that is not human readable.

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.

SQL select between two dates and time

You can use the column containing the date time field in WHERE clause, if your column is transactionDatTim then query is like below:

DECLARE @tempDate DATE -- note that this date and not DateTime
DECLARE @tempStartDateTime DATETIME, @tempEndDateTime DATETIME
SET @tempDate = GETDATE()
SET @tempEndDateTime = DATEADD(hh,19,CAST(@tempDate AS datetime))
SET @tempStartDateTime  = DATEADD(mi,-1,DATEADD(d,-1,@tempEndDateTime))


--the query
SELECT transaction
FROM test_tbl
WHERE transactionDatTim BETWEEN @tempStartDateTime  AND @tempEndDateTime

shorter version w/o temp variables will be

--the query
SELECT transaction
FROM test_tbl
WHERE transactionDatTim BETWEEN 
   DATEADD(mi,-((24-19)*60+1),CAST(CAST(GETDATE() AS DATE) AS datetime))  
    AND 
   DATEADD(hh,19,CAST(CAST(GETDATE() AS DATE) AS datetime))

Selecting between two dates

Do not use string functions on dates! Simply express the conditions as:

WHERE Trx_Status = 1 and
      Remit_Status = 3 and
      T.Payment_Date >= '2021-03-01' and
      T.Payment_Date < '2021-04-01'

Note that I changed the date comparisons to use >= and <. This is safer than BETWEEN, because it works for dates and datetimes with no changes.

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;