Question Regarding Fetching Records based on Date Range

Question

SELECT COUNT(td.timeSheetID) FROM TimeSheet t 
INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID 
WHERE (CONVERT(DateTime, t.setDate, 23) BETWEEN '2012-08-10' AND '2012-08-12') 
AND t.employeeID = 300

The above code returns the value i'm looking for. However, when i do the following, i get nothing:

    SELECT COUNT(td.timeSheetID) FROM TimeSheet t INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID 
    WHERE (CONVERT(DateTime, t.setDate, 23) = '2012-08-11') 
    AND t.employeeID = 300

setDate value in table looks as:

2012-08-11 18:00:19.000

My questions:

1. Why the code in first scenario works fine whereas in the second one doesn't work?

2. Why the code in the first scenario doesn't return a value when i put the actual range as BETWEEN '2012-08-11' AND '2012-08-11' ... How can i solve the problem, i don't time to be considered during the range/filtering. I'm only considered about date.

Answer 1

When you use CONVERT and change the value to a DATETIME, the time is not removed. So the compare to the date values will not match. Try the following to see what happens

SELECT CONVERT(DateTime, t.setDate, 23)
FROM TimeSheet t

To drop the time from datetime change the CONVERT expression so that the result casts to a varchar data type

CONVERT(varchar, t.setDate, 23)

Answer 2

• Don't write queries that use functions on columns to compare with constants. It makes them un-SARG-able and an eventual index on the setDate column cannot be used.
• Don't use literals where you can use a parameter
• If you must use literals for datetime, use the unseparated string format as is not language dependent and is always interpreted as ymd.

In conclusion:

SELECT COUNT(td.timeSheetID) FROM TimeSheet t 
INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID 
WHERE t.setDate BETWEEN @dateFrom AND @dateTo 
AND t.employeeID = @employeeId;

or (worse):

SELECT COUNT(td.timeSheetID) FROM TimeSheet t 
INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID 
WHERE t.setDate BETWEEN '20120810' AND '20120812'
AND t.employeeID = 300;

As for your question, the best way to scan a 'day' in a datetime range is to express the day as a range:

SELECT COUNT(td.timeSheetID) FROM TimeSheet t 
INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID 
WHERE t.setDate >= '20120811' AND t.setDate < '20120812'
AND t.employeeID = 300;

This will cover all hours of 20120811. Is it still better to parameters instead of literals, of course.

And finally, the real question will be if you have a covering index for this query. Time series, like a 'timesheet' table, are almost always interogated by daterange, so a clustered index by (employeeID, setDate) is probably a good choice. A second best choice is a nonclustered index on the same.