[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