[thelist] SQL: BETWEEN versus (Greater Than and Less Than)
Jason Handby
jason.handby at corestar.co.uk
Thu Mar 22 19:02:02 CDT 2007
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
More information about the thelist
mailing list