SQL Server convert to optimal short date -

what optimal short date convert in sql sever use in predicate.

i have date 2013-06-11 15:06:27.000 , want use short date part 2013-06-11 in predicate.

what best short date convert in sql server purpose?

msdn - date convert

for sql2005+:

note: select convert(datetime, convert(varchar, datetimecolumn, 12)) <operator> <const> isn't sargable! so, if have index on datetimecolumn sql server can't seek (index seek) on column. instead, sql server use index scan, clustered index scan or table scan.

if want filter rows on datetime column use datetimecolumn >= rangestart , datetimecolumn < rangeendor datetimecolumn between rangestart , rangeend predicates.

how can generated rangestart , rangeend ?

    declare @selecteddate datetime;     set     @selecteddate='2013-06-11 15:06:27.000';      select  dateadd(day,datediff(day,0,@selecteddate),0) start,             dateadd(day,datediff(day,0,@selecteddate)+1,0) [end 1],             dateadd(millisecond,-3,dateadd(day,datediff(day,0,@selecteddate)+1,0)) [end 2] 

note 2: datetime column, last millisecond can 1 of these {0,3,7} (see bol).

results:

start                   end 1                   end 2 ----------------------- ----------------------- ----------------------- 2013-06-11 00:00:00.000 2013-06-12 00:00:00.000 2013-06-11 23:59:59.997 

example #1:

...   h.orderdate>=dateadd(day,datediff(day,0,@selecteddate),0) ,     h.orderdate<dateadd(day,datediff(day,0,@selecteddate)+1,0) 

example #2:

...       h.orderdate>=dateadd(day,datediff(day,0,@selecteddate),0) ,     h.orderdate<=dateadd(millisecond,-3,dateadd(day,datediff(day,0,@selecteddate)+1,0)) 

example #3:

...   h.orderdate between dateadd(day,datediff(day,0,@selecteddate),0) , dateadd(millisecond,-3,dateadd(day,datediff(day,0,@selecteddate)+1,0))