Which Transact-SQL query should you use?

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 nonclustered 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?

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 nonclustered 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?

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()))

Explanation:
http://technet.microsoft.com/en-us/library/ms181034.aspx



Leave a Reply 7

Your email address will not be published. Required fields are marked *


Mohamed

Mohamed

CONVERT(DATE, GETDATE()) = today
DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) = tomorrow
then PurchaseTime > or = today and < tomorrow

R

R

C and D are both correct. However the question states that we should write the query in the most efficient manner. D is the answer

Wojtek

Wojtek

Indeed C would also bring the right results but it would not utilize the index.

Islam

Islam

B is the correct answer GETDATE() will return the date you wanted which is current date.

Rob

Rob

B is incorrect. 01/21/2016 19:00 is not equal to 01/21/2016. B does not handle the time portion of PurchaseTime

Marcin

Marcin

why the second condition? AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())).
I will use always curent day? Never more than.