SQL and the like

DateTime Lookups

Heres a little something that Simon Sabin mentioned at the London PASS user group last night that I wasnt aware of.

Ive lost count of the amount of queries I have that are some derivative of  >= Midnight of a day and < Midnight of day +1. In SQLServer 2008 the engine now does the work for you.

select *
  from Sales.SalesOrderHeader 
 where CAST(OrderDate as DATE) ='20010701'
 

No great surprises so far, but internally the engine has transformed it into a BETWEEN query. So, if you add an index

create index idxSalesOrderHeaderDate 
    on Sales.SalesOrderHeader(OrderDate) 
    include (SalesOrderId)

and then look at the query plan …

Fabulous, an index seek. How simple is that ?