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.

Show quoteHide quote

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/info_datetime.asp#Searching

Show quoteHide quote

On Wed, 21 Nov 2007 18:34:21 -0200, Paulo wrote:

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.

If you used datetime variable then

Where
datecol>=dateadd(day,datediff(day,0,@datevar),0) and
datecol<dateadd(day,datediff(day,0,@datevar),1)

Update Timeout and indexes
Performance of SQL Server Express
Snapshot size
The affinity mask specified does not match the CPU mask on this system
SQL Server Transaction Log growing