Is the same?

Select DateField from table where Month(DateField) = 11 and Year(DateField)
= 2007 and Day(DateField) = 21
and
Select DateField from table where DateField = '20071121'
What should I use'
Thanks a lot!The first will never use an index where as the second has a chance to use
one. Never use a function on a column in a WHERE clause if you can avoid it.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23FZmm3HLIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Select DateField from table where Month(DateField) = 11 and
> Year(DateField) = 2007 and Day(DateField) = 21
> and
> Select DateField from table where DateField = '20071121'
> What should I use'
> Thanks a lot!
>|||On Wed, 21 Nov 2007 18:34:21 -0200, Paulo wrote:

>Select DateField from table where Month(DateField) = 11 and Year(DateField)
>= 2007 and Day(DateField) = 21
>and
>Select DateField from table where DateField = '20071121'
>What should I use'
>Thanks a lot!
>
Hi Paulo,
Since SQL Server has no data type that stores only a date, you'll always
have to deal with the combination of date and time.
The first form will find all rows where the DateField represents some
moment during the 21st of november 2007. The second form will only find
rows representing midnight of that day.
These two are only the same if you are 100% sure that all datetime
values in the DateField column will have their time component equal to
midnight. In that case, the second version is (as Andrew already wrote)
potentially faster, because it can use an index if there is one.
If you can not be sure that the time component is always equal to
midnight, and you want to return all rows with the date component equal
to the 21st of november 2007, use this version that searches for all
moments in a range that starts at midnight and ends just before midnight
of the next day:
SELECT DateField
FROM table
WHERE DateField >= '20071121'
AND DateField < '20071122';
Recommended reading: Tibor Karaszi's ultimate guide to the datetime data
type at http://www.karaszi.com/SQLServer/info_datetime.asp.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Also, the first will pick up all rows from that day, regardless of that the
time option is. The
second will only pick up rows with time portion equals to 00:00:00.000. More
info at:
http://www.karaszi.com/SQLServer/in...e.asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0ZSZEILIHA.3992@.TK2MSFTNGP03.phx.gbl...
> The first will never use an index where as the second has a chance to use
one. Never use a
> function on a column in a WHERE clause if you can avoid it.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message news:%23FZmm3HLIHA.5360@.TK2M
SFTNGP03.phx.gbl...
>|||On Nov 22, 1:34 am, "Paulo" <prbs...@.uol.com.br> wrote:
> Select DateField from table where Month(DateField) = 11 and Year(DateField
)
> = 2007 and Day(DateField) = 21
> and
> Select DateField from table where DateField = '20071121'
> What should I use'
> Thanks a lot!
If you used datetime variable then
Where
datecol> =dateadd(day,datediff(day,0,@.datevar),0)
and
datecol<dateadd(day,datediff(day,0,@.datevar),1)