Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chetanb3
Yak Posting Veteran
52 Posts |
 Posted - 2011-05-31 : 02:26:46
|
| Which is better to use Between or <= and >= with dates. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-31 : 02:40:59
|
This is SQL Server 2005 isn't it?There is no datatype DATE only DATETIME.So you have to consider the time part in your query.May be you need something like WHERE mydate >= '20110501' and mydate < '20110601' to get the rest of the actual day included...Using BETWEEN there is no possibilty for a fine tuning like that.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
chetanb3
Yak Posting Veteran
52 Posts |
Posted - 2011-05-31 : 03:05:11
|
| I am not using time, I am just using dates. I wanted to know which is better in terms of performance |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-31 : 03:59:34
|
| I don't think there is a difference. I always use BETWEEN for better readability...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-31 : 15:59:24
|
| There is no performance difference - in fact, if you check the execution plan SQL Server converts a BETWEEN to >= and <=.The biggest issue with using BETWEEN is that it can lead to code that is not correct. Or, it leads to code where the criteria is no longer SARGABLE and eliminates the usability of any indexes on those columns.Take for example the following:WHERE datecolumn BETWEEN '20110401' AND '20110430'The assumption here is that you will get the full month, the truth is that you will miss everything that happens on the last day of the month. This is because the above is actually:WHERE datecolumn BETWEEN '20110401 00:00:00.000' AND '20110430 00:00:00.000'Anything that falls on 20110430 after midnight (the full day - in other words) will be excluded. Now, when this found - the developer often fixes it by using something like:WHERE dateadd(day, datediff(day, 0, datecolumn), 0) BETWEEN '20110401' AND '20110430'This works, but eliminates the ability to use an index on 'datecolumn' because of the function calls. So, then someone decides to modify the date parameterS and we get this:WHERE datecolumn BETWEEN '20110401 00:00:00.000' AND '20110430 23:59:59.997'Which again works - but, if the system is upgraded to 2008 and the data type is changed to datetime2 will now miss out on the last 3 milliseconds of the day.So, with all that said - if you use the following:WHERE datecolumn >= '20110401'AND datecolumn < dateadd(day, 1, '20110430')Not only are you assured of getting everything on the end date - but if the data type is changed it won't affect your code.Jeff |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-01 : 02:00:34
|
| As is with all functions and all sql queries you need to know the data and also know how the functions work before using them. But if you always use the <>= instead of between there will never be any doubt.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
|
|
BETWEEN vs <= and >=