another interesting post , thanks dude now I have become permanent subscriber of your SQL articles.great job.

JP
How HashMap works in Java

Would using BETWEEN be cleaner and just as fast?

I think you meant to use "BETWEEN" instead of "=" in the final version.

However, BETWEEN (due to it's inclusive-nature) potentially will make you miss entries that are in the last 999 ms of the day (**))

Hence I would rather use the folllowing, which will be just as good performance wise :

SELECT *
  FROM Purchasing.PurchaseOrderDetail
 WHERE ModifiedDate >=                Convert(datetime, CONVERT(varchar(30), @FindDate, 101), 101)
   AND ModifiedDate < DATEADD(day, 1, Convert(datetime, CONVERT(varchar(30), @FindDate, 101), 101))

PS: totally off-topic, but I've never seen anyone use CAST() and Convert() "intermixed" =) Personally I would suggest to always use Convert() as it allows you to specify the format in both directions.

(**: yes, I know, not exactly true do the 3ms granularity of the datetime datatype but if you knew that I'm sure you'll also catch my drift =)

my 2 cents. Roby

I don't have AdventureWorks database to try this on, but I think it would be faster to do the date functions outside of the query. Like this:

DECLARE @FindDate   DATETIME
    , @StartDate	DATETIME
    , @EndDate		DATETIME 

SET @FindDate = '2005/09/12 12:00:00'
SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0,@FindDate)) 
SET @EndDate = DATEADD(dd, 1, @StartDate)

SELECT *
FROM Purchasing.PurchaseOrderDetail
WHERE ModifiedDate >= @StartDate
    AND ModifiedDate < @EndDate 

I'm testing these scenarios and seeing no difference between any of the queries. Even the last query in the article that uses two equal comparisons instead of a between, all have 66 logical reads. Personally, I would have gone with the solution that glesser posted above. But after looking at the CPU and Elapsed time I may be changing my mind.

usually i would like to avoid, "cast" and "convert" in datetime comparisions. Suppose, we can write that expression like below..

Where ModifiedDate >= DATEADD(dd,datediff(dd,0,@FindDate)),0) and ModifiedDate <  DATEADD(dd,datediff(dd,0,@FindDate),1)

I like this concept very much. Yes, when i am a beginner, i used to code for shorter ways,even if there is more better ways in terms of performance.

One common scenario is, some times we will pass comma-seperated values, and will find the rows with those values in tables. I used to use charindex() function previously.

where charindex(',' + ColName + ',' , ',' + @Vals + ',') > 0

However, this is not elegant way, If colName has index, it won't use that index.

The better way is by using split function, split that @vals, and then join with that table. This is long. But is more elegant and will gives better performance.....

@glesser : doing the "conversion to midnight" outside the query won't make much of a difference performance-wise in this case as the Query Optimizer will recognize this as "a function on a constant being just another constant' and act accordingly. But I agree that doing it outside of the query is good practice and makes things more readable indeed, especially as you've named them @Start and @End.

BTW : I know that abusing Convert() for the sake of stripping the hours/minutes/seconds/ms from a datetime value is, well, wrong =) But I didn't want to change the original query on any other element than the one I was trying to point out. That said, I had no idea about the DateAdd(dd, 0, DateDiff(dd, 0, @x)), I always stripped hours, minutes, seconds & ms one by one, this is so much cleaner, nice ! Sometimes Shorter is smarter =P

o_lander : Possibly you don't have index on "ModifiedDate", create a non clustered index on said column and then execute all three quires. Core theme is that you must avoid function on left side of your WHERE condition, because function will mislead query analyzer and proper index will not be used and resultant query will perform slow.

Very good concept. Yes, moving the application of the function from the value being compared to the value being compared with (i.e. from the LVALUE to the RVALUE) would make a difference.

Jacob Sebastian has high-lighted this during his session in TechEd 2011. Of course he was trying to emphasize the importance of choosing the right data-types and the impact of functions on performance of a query (Read summary here: http://beyondrelational.com/blogs/nakul/archive/2011/04/06/t-sql-worst-practices-a-compilation-from-jacob-s-session-at-tech-ed-2011-part-02-practices-that-affect-performance.aspx)

Anyway, coming to the problem at hand, I would avoid using CAST & CONVERT as far as possible when working dates - just as RamiReddy suggests. In my opinion, CAST & CONVERT are for persentation, and must be used when returning data back to the calling application - not during business logic computation (that's not to say that they are not absolutely essential sometimes). When working with scalar values, I would prefer the approach suggested by @glesser.

At the end I would say that essentially, its all about how best we can convert our scans to seeks - and that is very well illustrated by this post.

Thanks & Regards,

Nakul Vachhrajani

http://beyondrelational.com/blogs/nakul/default.aspx

Be Courteous. Drive responsibly.

i prefer this

AND ModifiedDate < DATEADD(d, 1, CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME))

over this

AND ModifiedDate <= DATEADD(SS, 86399, CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME))

You should avoid using seconds because datetime data type is accurate upto milliseconds and datetime2 datatype is accurate upto microseconds. Use day for simplicity and to avoid confusion.

Regards,
Syed Muhammad Yasir

I like the comment above.

Regards,
Muntazer Mehdi