//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: General SQL Server & Access Articles :: Should I use BETWEEN in my database queries?


Should I use BETWEEN in my database queries?

In my opinion, NO. For at least four reasons:
  • 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.
 
* For information on the preferred date format you should be using to pass DATETIME / SMALLDATETIME values to SQL Server, see Article #2023 and the ultimate guide to the datetime datat....

Related Articles

Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?

 

 


Created: 4/15/2002 | Last Updated: 6/23/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (118)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...