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


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Compare date as date not as varchar

Apr 1 2011 4:11AM by Madhivanan   

When I was viewing someone's code, I have noticed that the date values were not compared as dates but they were converted to varchar before comparision

Suppose, you want find out the objects that were created in February 2011. The correct code is

select 
	name 
from 
	sys.objects
where 
	create_date>='20110201' and create_date<'20110301' 

But the code I was viewing had the following code

select 
	name 
from 
	sys.objects
where 
	convert(varchar(10),create_date,120 ) BETWEEN '2010-02-01' AND '2011-02-28'

The problem with the above is that it will not give correct results as create_date has time part too. Also it is possible that invalid dates can be given in the where clause like below

select 
	name 
from 
	sys.objects
where 
	convert(varchar(10),create_date,120 ) BETWEEN '2010-02-01' AND '2011-02-400'

As you know, no month will have day of 400 but the code does work and will not give any error. So it is always better to compare DATE values as DATE and not as VARCHAR.

I have laready posted a series of posts about DATETIME column and it's handling in the WHERE caluse. Make sure to read the following posts too
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-ii.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-iii.aspx

Tags: t-sql, sql_server, datetime, sqlserver, tsql, BRH,


Madhivanan
3 · 40% · 12899
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

5  Comments  

  • Very informative article man , I didn't know about this , thanks for great work.

    Javin

    commented on Apr 26 2011 10:11AM
    Javin Paul
    1769 · 0% · 11
  • Thanks for the feedback javabuddy

    commented on May 23 2011 6:12AM
    Madhivanan
    3 · 40% · 12899
  • What if your dates are in variables, like:

    Declare @EndDate as datetime
    Declare @StartDate as datetime
    
    Set @StartDate = '20111101'
    Set @EndDate = '20111130'
    
    select 
        name 
    from 
        sys.objects
    where 
        create_date >= @StartDate and create_date < @EndDate
    

    SQL Server doesn't want to use the index when that's done. If you have a stored procedure where the dates are passed in, how do you get the index on the datetime column used?

    commented on Dec 7 2011 6:52PM
    darickard
    2746 · 0% · 4
  • darickard, there is no problem with using variables as you showed. The point is that do not convert column into varchar and compare.

    commented on Dec 7 2011 11:45PM
    Madhivanan
    3 · 40% · 12899
  • On SQL Server 2005, if I run such a query on a fairly large table (~100M records) where the date column has an index on it, if the date variables are used, SQL Server does an index scan instead of a seek. If the date literals are used in the where clause, like you have in your example, it does a seek. The seek is very fast, the scan takes a very long time.

    commented on Dec 8 2011 3:40PM
    darickard
    2746 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"Compare date as date not as varchar" rated 5 out of 5 by 2 readers
Compare date as date not as varchar , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]