Hi experts, Which one is better performance wise , please ? _od.transaction_datetime between @startDate and @endDate_ OR _AND od.transaction_datetime >= @startDate AND od.transaction_datetime <= dateadd(day,1,@enddate)_
Your first will likely be faster by a smidge due to the additional day you are querying in the second query. If you remove that dateadd and the subsequent additional day, then the two will be on par with each other.
If your raw data (transaction_datetime) is of DATETIME data type, first thing you need to do is check if it actually has a TIME part. If it does have TIME, then you need to be careful in setting your WHERE clause. When you use: WHERE transaction_datetime BETWEEN @startDate and @endDate MSSQL will automatically cast @startDate and @endDate to DATETIME data type. This is called "implicit casting". It does this because your raw date (transaction_datetime) is of DATETIME data type. So, lets suppose you want to retrieve data for July 2014. WHERE transaction_datetime BETWEEN @startDate and @endDate ... given your DATE variables becomes... WHERE transaction_datetime BETWEEN 2014-07-01 and 2014-07-31 ... which is implicitly cast to DATETIME as... WHERE transaction_datetime BETWEEN 2014-07-01 00:00:00.000 and 2014-07-31 00:00:00.000 And this is the reason why you will not get a complete result set if your data does have TIME part. Because even if it has a single milisecond (i.e. 2014-07-31 00:00:00.001), then it is greater than 2014-07-31 00:00:00.000 and will therefore be absent in your result set. A few things you can do..... You could actually CAST(transaction_datetime) AS date. That will effectively get rid of the TIME part and will make your BETWEEN work as is. However, it is not the best approach from a performance perspective, because you are performing an unnecessary CAST operation on potentially your entire data. As far as I know, "BETWEEN" versus ">= AND <=" are equivalent. The problem in your code is your "BETWEEN" is not coded properly (because your data has TIME constituent). Your ">= AND <=" IS coded properly because of the addition of DATEADD. Adding a day will fix your end point as follows. WHERE transaction_datetime BETWEEN @startDate and DATEADD(day, 1, @endDate) ... given your DATE variables becomes... WHERE transaction_datetime BETWEEN 2014-07-01 and DATEADD(day, 1, 2014-07-31) ... after adding a day ... WHERE transaction_datetime BETWEEN 2014-07-01 and 2014-08-01 ... which is implicitly cast to DATETIME as... WHERE transaction_datetime BETWEEN 2014-07-01 00:00:00.000 and 2014-08-01 00:00:00.000 The above will produce the exact same result as the ">= and <=" statement. Performance wise, tey are identical as far as I know. However, notice ">= and <=" may not be what you really need. I think your end point should be a "<" as opposed to a "<=". In my opinion, the below would be the best course of action: WHERE transaction_datetime >= @startDate and transaction_datetime < DATEADD(day, 1, @endDate) Or in our illustrative example: WHERE transaction_datetime >= 2014-07-01 00:00:00.000 and transaction_datetime < 2014-08-01 00:00:00.000