Contact Us
Site Map

Search

• In my opinion, BETWEEN is ambiguous, at least in SQL Server. It is unclear to the casual observer whether "...BETWEEN '20020104' AND '20020415' will include a row created at 2002-04-15 at 4:00 am; I have observed that many people EXPECT such a query to return results for all times on April 15th, not just those that have a time value of exactly midnight. Much clearer to use >= '20020401' AND < '20020415'. This works better because all datetime values are compared to midnight, and you can easily see exactly where your range both ends and begins. This can also be a problem when you think "Word BETWEEN C AND D" and you have a varchar value of "Data," because often people *think* in terms of all the words that start with the letter C and all the words that start with the letter D. 
   
  Another way BETWEEN is ambiguous in SQL Server is that it includes the values at both ranges. Depending on who you talk to and the context, the word "between" in English often does not include the endpoints (e.g. between your teeth, between the lines). Again, using (> or >=) and (< or <=) will be more explicit and straightforward, even if it requires all the effort of typing out the column name one additional time. 
   
  
• ORDER MATTERS. In some languages, order doesn't matter. However, since WHERE foo BETWEEN 4 AND 6 gets translated to WHERE foo >= 4 AND foo <= 6, you will quickly fnd that if you swap your values in the BETWEEN clause (which is common, and as stated, some languages will forgive you), your results will change. Try running WHERE foo BETWEEN 6 AND 4 and you will see what I mean. 
   
  
• For datetime-related queries like 'give me all the rows for this month', you tell me which is easier to construct: 
   
  

  WHERE dt BETWEEN '2002-04-01T00:00:00.000' AND '2002-04-30T23:59:59.997'

   
   
  

  WHERE dt >= '20020401' AND dt < '20020501'

   
  The latter is obviously easier to create by hand*, and is slightly easier even when doing so dynamically: 
   
  

  DECLARE @start SMALLDATETIME, @end SMALLDATETIME  SET @start = 0 + DATEDIFF(DAY, 0, GETDATE()) - (DAY(GETDATE())-1)   SET @end = DATEADD(MONTH, 1, @start)  SELECT ... WHERE dt >= @start AND dt < @end

   
  vs. 
   
  

  DECLARE @start SMALLDATETIME, @end DATETIME -- need precision of datetime here  SET @start = 0 + DATEDIFF(DAY, 0, GETDATE()) - (DAY(GETDATE())-1)   SET @end = DATEADD(MILLISECOND, -3, DATEADD(MONTH, 1, @start))  SELECT ... WHERE dt >= @start AND dt < @end

   
  (Of course, when I originally wrote this, I was thinking that you might have the courage to generate the end point using string formatting. *Shudder*) 
   
  
• When you are using other clauses in your query, you also have to remember to wrap the BETWEEN clause in brackets so that its AND isn't confused with other ANDs in the WHERE clause. See Article #2148 for more information on using OR and AND in a complex query, and remember that the order the engine uses to process a WHERE clause is not necessarily in the order the WHERE clause is written.