Madhivanan's TSQL Blog

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


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

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

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (385) · Preferences

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,

Previous: Trigger and number of rows affected
Next: Alias names - SQL Server Vs Oracle

Madhivanan

3 · 39% · 12429

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is 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

1719 · 0% · 11

Thanks for the feedback javabuddy

commented on May 23 2011 6:12AM

Madhivanan

3 · 39% · 12429

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

2638 · 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 · 39% · 12429

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

Madhivanan's TSQL Blog

Compare date as date not as varchar

5 Comments

Your Comment