Good post. But note that COL BETWEEN @a and @b is more readable and understandable than COL>=@a and COL<=@b. It is becuase BETWEEN is plain english and easy to understand. Also you dont need to repeat the column twice for the comaprison. Also if any of the operators <,= or > is missed or interpreted wrongly, the result is not accurate.

But I prefer using >= and <=. It is becuase it is very useful where compare datetime values. You need to specify time part when you use BETWEEN.

Exactly my point, Madhivanan when I attempt to answer the question whether BETWEEN is really required.

Thank-you for your valuable comments. It is good to know that I am not the only one who gets nervous around people using comparison operators without reviewing their code and ultimately resulting in logical bugs in the application.

Here are more good examples of why you should neve, EVER use BETWEEN when searching for date ranges... and some GOOD ALTERNATIVES with examples.

http://garybhaley.com/ComparingDatesInSQL.pdf

TheSQLWhisperer

@SQL WHisperer: As I mentioned, the time component will need additional precautions on the part of the developer - there is absolutely no doubt about that. The white paper you point out points out the issues that might occur in detail. It's a good one, and thank-you for sharing.

However, in most legacy systems, the date and time components are stored separately (and apparently, the need to do still exists because the new DATE type was just introduced). In such cases, the BETWEEN is a much safer alternate.

BETWEEN has it's uses, and therefore, like almost everything on this planet needs to be used with proper judgement and care.

For my testing I found converting the target column to DATE for time insensitive comparison performed over 55k rows actually performed better than using full DateTime with filter values inclusive of the time needed, and I find both to be accurate. I did get a good laugh about the idea of comparison based on converting the date to a string...

SELECT * 
FROM HumanResources.Employee
WHERE convert(date, HireDate) BETWEEN '2001-01-01' AND '2001-12-31';

Verses

SELECT * 
FROM HumanResources.Employee
WHERE HireDate BETWEEN '2001-01-01 00:00:00.000' AND '2001-12-31 23:59:59.997';

Just a heads-up... converting dates to strings, and then comparing the strings uses a a LOT of resources, especially if enough queries are pounding your database and cycling through your cache quickly. Take advantage of your execution plan by NOT comparing strings. Again, take a minute and read this article. It's based on many lessons learned, and full of good (free) info.

http://garybhaley.com/ComparingDatesInSQL.pdf

TheSQLWhisperer

@SQL Whisperer: I agree. Conversion to strings is a bad idea here.

In fact, unless absolutely necessary, I try never to convert to strings for the same reason - it takes a lot of resources.

Hi guys, I have seen freshers assuming that comparison value order does not matter, but that is not the case. Always smaller value should be first followed by larger value This confusion arises due to English meaning of between. Agree that this situation will not come with an experienced person.

To avoid this, best way is if we can write the code using >= and <=

Thanks Jeetendra

FYI:The current ANSI syntax is:

::= [NOT] BETWEEN [ASYMMETRIC | SYMMETRIC] AND

The ASYMMETRIC option is the default and it is the curent behavior. The SYMMETRIC opton swaps the two s so it tests "in both directions" at once. ACCESS had this as a dialect and we considered it at one time in ANSI X3H2