BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00
The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP
|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>