Using DATE part of datetime column in WHERE clause

Question

I'm building a POS system, where I collect the date & time that the event took place. I would like to pull some reports by only using the DATE part of the datetime column in my SQL queries.

My current query looks like:

SELECT * FROM tbl_item_trans
WHERE  (date_time BETWEEN @start AND @end)
ORDER BY date_time DESC, code

However, if I put in two dates e.g. 14th and 15th September it only pulls data from 14. If I want to include 15th September transactions, the end date would have to be 16th September.

How can I solve this problem? It would be better also if I only used the DATE part of the datetime column.

the @start and @end variables use the date data type, e.g. '2017-09-16'.

Answer 1

_{Community wiki answer:}

Try an inclusive start date and exclusive end date:

date_time >= @start AND date_time < DATEADD(day, 1, @end)

See also What do BETWEEN and the devil have in common? by Aaron Bertrand.

---

Sargability wouldn't be a problem if the query is written to use CAST(date_time AS DATE), since SQL Server will include a helper covering seek predicate. See Understanding SARGability (to make your queries run faster) by Rob Farley (recorded at SQLBits VII in 2010) which demoed this. Note however that the conversion might still affect the accuracy of cardinality estimates.