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

[Jason Handby]

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