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

Anthony Baratta anthony at baratta.com
Thu Mar 22 19:24:09 CDT 2007


P.S. The need for two variables there is because our web page asks the user for a start and end date. We can't just condense it to one date, and with the testing we were just testing against one date. But the range could be more than one day.

-----Original message-----
From: "Jason Handby" jason.handby at corestar.co.uk
Date: Thu, 22 Mar 2007 17:02:02 -0700
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL: BETWEEN versus (Greater Than and Less Than)

> I wrote:
> 
> 
> > > convert(char(8), create_dt, 112)  >= '20070319' and 
> > > convert(char(8), create_dt, 112)  <= '20070319'
> > 
> > 
> > Didn't you mean to write
> > 
> >   convert(char(8), create_dt, 112)  >= '20070319' and 
> >     convert(char(8), create_dt, 112)  < '20070320'
> > 
> > 
> > ...?
> 
> 
> Actually that's a bit long-winded -- you can just write
> 
>    convert(char(8), create_dt, 112)  = '20070319'
> 
> But this will mean calling convert for every row of your table, in order
> to generate a value that can be compared to your date string.
> 
> 
> I'm not Rudy or Ken, but I bet it's a lot more efficient to do it the
> other way around, so that the conversion only has to happen once for the
> entire result set and so that you can make use of any index you might
> have on the date column:
> 
>     create_dt >= convert(DATETIME, '20070319') AND create_dt <
> convert(DATETIME, '20070320')
> 
> 
> 
> Jason
> -- 
> 
> * * 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