Querying Microsoft SQL Server 2012/2014 — Question 8

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column.
The business team wants a report that displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

Answer options

• A. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
• C. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))
• D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct answer: D

Explanation

The correct answer is D because it efficiently counts all orders placed today by checking if the OrderTime is greater than or equal to the start of the day and less than the start of the next day. Option A incorrectly checks for equality with a date, which will fail for any orders not placed exactly at midnight. Option B is incorrect as it compares OrderTime to the exact current date and time, which will not match most entries. Option C uses string conversion, which is less efficient than direct date comparisons.