http://sqlfiddle.com/#!18/639ec/2
CREATE TABLE TEST
(
ID INT,
OrderNo int,
DateReceived datetime
)
INSERT INTO TEST (ID,OrderNo,DateReceived)
VALUES ('1', '3454', '07-20-2018 00:00:00')
Query:
DECLARE @StartDate datetime,
@EndDate datetime,
@FlooredStart datetime ,
@FlooredEnd datetime
SET @StartDate = '07-20-18'
SET @EndDate = '07-20-18'
SET @FlooredStart = CAST(FLOOR(CAST(@startDate AS FLOAT)) AS DATETIME)
SET @FlooredEnd = DATEADD(d, 1, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS DATETIME))
SELECT * FROM TEST
WHERE DateReceived = @FlooredStart and DateReceived < @FlooredEnd
In my live version if the date received is left blank it defaults to the time 12:00:00AM of that day. So for this example if i search for orders on the 07-20-18 it wont return the order that was placed at 12:00:00AM of the 07-20-18.
So i figure to add >= to the clause
DECLARE @StartDate datetime,
@EndDate datetime,
@FlooredStart datetime ,
@FlooredEnd datetime
SET @StartDate = '07-20-18'
SET @EndDate = '07-20-18'
SET @FlooredStart = CAST(FLOOR(CAST(@startDate AS FLOAT)) AS DATETIME)
SET @FlooredEnd = DATEADD(d, 1, CAST(FLOOR(CAST(@endDate AS FLOAT)) AS
DATETIME))
SELECT * FROM TEST
-- WHERE DateReceived BETWEEN @StartDate AND @EndDate
WHERE DateReceived >= @FlooredStart and DateReceived < @FlooredEnd
Result:
| ID | OrderNo | DateReceived |
|----|---------|----------------------|
| 1 | 3454 | 2018-07-20T00:00:00Z |
I was just wondering if i had this logic correct? And can anyone explain to me exactly what the floor() function is doing. I understand that its calculating the first day of year/month but is that needed here? I looked online and i could'nt find a definitive answer anywhere.
Thanks
Instead of using floor to "truncate" the time part, use the date-only date type :
DECLARE @StartDate date = '20180720', @EndDate date='20180720'
SELECT * FROM TEST
WHERE cast(DateReceived date) between @startdate and @enddate
or, for a single date only :
SELECT * FROM TEST
WHERE cast(DateReceived date) = @startdate
Note that I'm using the unseparated date literal. This is the only unambiguous date format. The other unambiguous format is the full ISO8601 format for the datetime types. Two-digit years are just begging for incorrect interpretation way.
cast(DateReceived date) will convert the datetime values in DateReceived into date values. Usually, it's a bad idea to apply functions to fields because it prevents the query optimizer to use any indexes. SQL Server understands cast( ... as date) though and converts :
cast(DateReceived date) = @startdate
To a range query equivalent to DateReceived between @startdate at 00:00 but before the next day at 00:00 , allowing it to use any indexes on DateReceived