Tsql >= and <= operators not filtering correctly?

phoeneous

Trying to filter either directly from query or from ssrs and for some reason, the @begindate and @enddate parameters are not including the first and last days.

For example:

select * from orders where orderdate >= @begindate AND orderdate <= @enddate

If I choose 09/01/2014 for @begindate and 09/30/2014 as @enddate only the records from 09/02 to 09/29 are included, any orders on 09/01 or 09/30 are not retrieved. I've also tried using between but same results. Am I using the operators incorrectly?

NotHackingYou

What is the data type of column? Is it date or datetime?

For example, you are searching for records between 9/1/2014 and 9/30/2014

You would expect to find hits for the following

9/1/2014 1:00AM

9/1/2014 10:00 PM

9/16/2014 2:00 AM

9/30/2014 1:00 AM

But it sounds like you aren't getting the 9/1's or the 9/30's.

This is because if the data type is DATETIME your supplied value of 9/30/2014 is implicitly cast to 9/30/2014 00:00:00, which is the very beginning of the day.

Try modifying your query to set @endDate to '9/30/2014 23:59:59' and see if you get the expected results. The 9/1's should be showing with your implicit cast so something else might be going on here - what time do they show?

Edit: Try this quick test on for size

CREATE TABLE #DateTest
(
testDate DATETIME
)


INSERT INTO #DateTest (testDate)
VALUES('9/1/2014'),
('9/1/2014 00:01:00'),
('9/15/2014 2:35:23'),
('9/30/2014'),
('9/30/2014 00:01:00')


SELECT * FROM #DateTest ORDER BY testDate


--Returns only 4 rows. The row after 9/30/2014 00:00:00 is not shown
SELECT * FROM #DateTest WHERE testDate >= '9/1/2014' AND testDate <= '9/30/2014'


--Returns all 5 rows
SELECT * FROM #DateTest WHERE testDate >= '9/1/2014 00:00:00' AND testDate <= '9/30/2014 23:59:59'

phoeneous

Thanks for quick reply. The column is datetime. If I set @begindate to 2014-09-01 00:00:00 and @enddate to 2014-09-30 23:59:59 then all records are retrieved. Problem is, this is for an ssrs report and the user is bound to just the datetime parameter selection, they have no control over entering the time like 23:59:59. The parameter datatype in ssrs is Date/Time which I assume is applying 00:00:00 to @enddate which makes sense. So how I guess I'll need an expression for @enddate to include 23:59:59.

NotHackingYou

It's a bit hacky but you could CAST the column to match their input (strip off the time from the data before you compare). Try this query aganst the test table. This query will be slower than the other ones. This may or may not be a factor for you.

SELECT * FROM #DateTest WHERE testDate >= '9/1/2014 00:00:00' AND CAST(testDate AS DATE) <= '9/30/2014'

phoeneous

This works:

AND (OrderDate BETWEEN (@StartDate ) AND (@EndDate + '23:59:59'))

NotHackingYou

Great idea! You might try fixing up @endDate prior to your use. I'm not sure if the concatenation excludes @endDate from being used as a search argument for orderDate (bypassing any index you might have on OrderDate). If you SET @EndDate = @EndDate + '23:59:59' prior to your search query, then you're sure to be a search argument and have maximum efficiency.

phoeneous

Understood, thanks for the help.