[thelist] SQL: BETWEEN versus (Greater Than and Less Than)

Anthony Baratta anthony at baratta.com
Fri Mar 23 12:00:39 CDT 2007


So basically you are saying "Between" sucks. ;-)

-----Original message-----
From: "r937" rudy at r937.com
Date: Fri, 23 Mar 2007 08:58:11 -0700
To: thelist at lists.evolt.org
Subject: [thelist]  SQL: BETWEEN versus (Greater Than and Less Than)

> sorry i missed the earlier excitement in this thread
> 
> performing a function on a table column and comparing the result to a static
> value is a sure way to force the optimizer into table scans
> 
> put the column on the left side of the operator, and all the calculations
> (if necessary) on the right
> 
> (the "if necessary" comment refers to constructions like
> DATEADD(day,1,GETDATE()) to represent tomorrow -- obviously, in a stored
> proc you'd pre-calculate the value)
> 
> jason had the right idea with this --
> 
>    where create_dt >= convert(DATETIME, '20070319')
>      and create_dt  < convert(DATETIME, '20070320')
> 
> but sql server is smart enough to recognize date strings, so you really
> don't need to do an explicit conversion ...
> 
>    where create_dt >= '20070319'
>      and create_dt  < '20070320'
> 
> using "greater than or equal" together with "less than" makes it a half-open
> interval, which is the easiest way to construct the range when datetime
> values (rather than date only) are involved
> 
> rudy
> http://r937.com/ 
> 
> 
> 
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
> 



More information about the thelist mailing list