Querying Microsoft SQL Server 2012/2014 — Question 5

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made 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 Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct answer: D

Explanation

The correct answer is D because it accurately captures all purchases made within the current day by using a range that starts from the beginning of today and ends just before the start of tomorrow. Option A is incorrect as it only checks for purchases that exactly match the date, which won't account for the time. Option B is wrong because it compares against the current date and time, which will not match any records. Option C is inefficient due to unnecessary conversion and may cause performance issues.