Find all rows WHERE DATETIME withing DATE

Question

Find all rows WHERE DATETIME withing DATE

mario17

SSCertifiable

Points: 6079
More actions
May 19, 2014 at 5:01 pm

#291494

Hi,
what would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
mARIO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Rob Farley has an interesting article here which discusses SARGability of functions on search predicates. One of the functions which doesn't destroy SARGability is CAST(column AS DATE), so your first query can in fact use an index seek.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

mario17 (5/19/2014)
Hi,
what would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
mARIO

BE VERY CAREFUL HERE!!! Your two queries are ONLY identical because you have declared @date to be a DATE datatype! I have seen this mistake at clients when used on other date(time) data types.

I also STRONGLY recommend you get in the habit of NEVER wrapping a column in a function in a where clause when a suitable replacement form exists that does not do so. Humans do what they are used to doing, and slipping on that one thing in an important piece of code can easily cost you multiple orders of magnitude performance hit AND crush your concurrency.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mario17 SSCertifiable Points: 6079 More actions · Answer 3

Thanks, all guys for your input.

I dont' have an option to change anything in source table (TT), biz behind this snippet is to select all orders for given day (with real time portion) for my report, @Date is from parameter, I can made as datetime and provide range like from '2012-1-12 00:00:00.000' thru '2012-1-12 23:59:59.999' .

or

declare @DATE DATE = '01/14/2014'

declare @DATENEXT DATE = DATEADD(D,1,@DATE)

--A:

SELECT * FROM TT

WHERE TT.DATETIME >= @DATE AND TT.DATETIME < @DATENEXT

mario