Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

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'
Read More..   [76 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Paresh Prajapati
6 · 23% · 7465
3
 
3
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

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 · 40% · 12972
  • Agreed with Ramireddy. There were 3 points I emphasised in the session.

    1. 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.
    2. 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.
    3. 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
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"Table scan or Unexpected output due to improper where condition applied for DateTime filter" rated 5 out of 5 by 3 readers
Table scan or Unexpected output due to improper where condition applied for DateTime filter , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]