Exam 70-765 topic 3 question 82 discussion

Actual exam question from Microsoft's 70-765

Question #: 82
Topic #: 3

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?

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

Show Suggested Answer

Suggested Answer: D 🗳️
To compare a time with date we must use >= and > operators, and not the = operator.
Incorrect Answers:
A: The in WHERE clause there is an incorrect comparison between time and a date, as equality (=) is used.
References:
http://technet.microsoft.com/en-us/library/ms181034.aspx

by Varad at Oct. 29, 2019, 9:03 p.m.

Comments

Submit Cancel

huzein

4 years, 6 months ago

C is correct, because with converting to format 112 we are on both sides on the current day. D is wrong, because D also includes orders from the days after today. A and B are obviously wrong.

upvoted 2 times

...

DudeHere

5 years, 2 months ago

I ran the code: SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) It does only return results when used with >=

upvoted 1 times

...

DudeHere

5 years, 2 months ago

"To compare a time with date we must use >= and > operators, and not the = operator." is 100% wrong. lol

upvoted 1 times

axdev

4 years, 11 months ago

Datetime has a time part so it equals to a date field only at 00:00:00.000 and it make sense to use >= and >.

upvoted 2 times

...

tmt

5 years, 3 months ago

C. 12 112 yyyymmdd ISO Refer convert function

upvoted 3 times

Hoglet

4 years, 5 months ago

This is face from efficient for 2 reasons 1. If you use a function on the indexed column, the optimiser can’t use that index. 2. CPU overhead of the function \ data type conversion The answer is D, if they displayed the whole answer

upvoted 1 times

...

Varad

5 years, 8 months ago

D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

upvoted 2 times

...