Just Learned - Table scan or Unexpected output due to improper where condition applied for DateTime filter

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Table scan or Unexpected output due to improper where condition applied for DateTime filter

Jun 11 2011 12:12PM by Paresh Prajapati

When you are using date filter with queries like find the records from tables for the particular year/month.

Should not use ,

#1. -- This cause to table scan
select *  from OrderDetails
where datepart(month,Orderdate) =?
and datepart(year,Orderdate) = ?

#2. -- This cause to unexpected output 
Select * from OrderDetails
where convert(varchar,OrderDate,112) between
'20110101' and '20110131'

We should use

#1.
Select * from OrderDetails
where convert(varchar,Orderdate,112) >= '20110101' 
and convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

#2.
Select * from OrderDetails
where cast (OrderDate as datetime) between
'2011-01-01 00:00:00.000' and '2011-01-31 23:59:59.900'

4 Comments

Why do we are casting to either varchar or datetime in those solutions???? I always will prefer not to use any cast/convert for column. Instead if any cast or converts required, do it for the variables. Below solutions should do......

Select * from OrderDetails where Orderdate >= '20110101' and Orderdate < '20110201' -- EndDate + 1

Select * from OrderDetails where OrderDate between '2011-01-01 00:00:00.000' and '2011-01-31 23:59:59.900'

commented on Jun 11 2011 3:53PM

Ramireddy

2 · 41% · 12972

Agreed with Ramireddy. There were 3 points I emphasised in the session.

It is not recommended apply a function or expression around a column used in the filter part (WHERE, JOIN etc) of a query. If we do so, any index that may exist on the column may not be used.
When dealing with DATATIME columns, always consider the possibility of having TIME values in the column. Even if TIME is not expected to be inserted in the column at design time, there is all possibility that it is added in the future. So when you want to filter data between two days, the condition used for the "enddate" should consider the end of the date (23:59:59.997). An easy way to do this is to find the next date and apply a 'less than' condition.
The query processor of SQL Server 2008 is capable of using an index on a DATETIME column even when you convert the DATETIME column to DATE. For example, WHERE CAST(saledate AS DATE) = @somevalue can use any index that exists in the saledate column (which is a DATETIME column)

commented on Jun 11 2011 11:21PM