Char vs Date comparision...convert(varchar(10),task_date,101) >= convert(varchar(10),GetDate(),101) vs task_date > GetDate()

Question

Char vs Date comparision...convert(varchar(10),task_date,101) >= convert(varchar(10),GetDate(),101) vs task_date > GetDate()

ajashadi

Ten Centuries

Points: 1017
More actions
January 23, 2007 at 9:19 am

#122741

Hi,
I came across a bug in our application in which tasks for admin are shown where the task_date is greater than current date. Since we have convert to varchar function, we were not getting right results i.e. tasks with task_date of 12/22/06 , 12/24/06 & so on were being shown when the current date is 01/23/07. My feeling is that I should compare dates directly instead of comparing them after converting to varchar i.e task_date > GetDate() i.e todays date. Anyone would like to share there experience?
My assumption is that since character 12/24/06 is greater the character 01/23/07 i.e 12 greater than 01 & so on, the result we get is 12/24/06 instead of actual expected result i.e 01/23/07. Does this assumption makes sense?
another example 12/29/2006 vs 12/28/2007.. I think o/p will be 12/29/2006 instead of 12/28/2007 since 29 > 28 char wise.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i encountered this issue similarly when sorting a formatted date in a grid; because it is text, it would sour by month, so all the 01/01/200X are together, followed by 01/02/200x's.

01/01/2002

01/01/2003

01/01/2004

01/02/2003

01/02/2005;

I can see two possible things you can do here, ORDER BY convert(datetime,somecolumn), or use a text based column in YYYYMMDD format instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Russel Loski SSCertifiable Points: 7059 More actions · Answer 2

I would prefer to compare dates. You could recast the string date value back to date. Otherwise, I prefer to use the YYYYMMDD format.

Russel Loski, MCSE Business Intelligence, Data Platform

ajashadi Ten Centuries Points: 1017 More actions · Answer 3

Yeah, I guess I will stick to date comparision. Here is what I have done :

convert(datetime,task_date) >= convert(datetime,getdate()).

lets say task_date is datetime type column and I use select convert(varchar(10),task_date,101) as task_date from test. When I use task_date for comparision in where clause i.e. task_date >= getdate(), I am I using char type task_date or date time type. If its char type, then I guess i need to use convert to cast it as datetime datatype.

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 4

If your project not suppose to stay a toy in your sandbox always do performance test before apply any solution.

SELECT TOP 1000000 IDENTITY(int, 1,1) as ID, cast(NULL as varchar(10)) as CharDate, cast(NULL as datetime) as DateDate

INTO dbo.TestPerformance

FROM syscolumns c1, syscolumns c2, syscolumns c3

CREATE INDEX IX_CharDate ON dbo.TestPerformance(CharDate)

CREATE INDEX IX_DateDate ON dbo.TestPerformance(DateDate)

UPDATE dbo.TestPerformance

SET CharDate = CONVERT(varchar(10), dateadd(hh, datediff(hh, 0, GETDATE())+ 3000 - id, 0) , 101),

DateDate = dateadd(dd, datediff(dd, 0, dateadd(hh, datediff(hh, 0, GETDATE())+ 100 - id, 0)), 0)

select * from dbo.TestPerformance

WHERE convert(datetime,CharDate) >= convert(datetime,getdate())

select * from dbo.TestPerformance

WHERE DateDate >= getdate()

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Based on your test table, there both queries will do a table scan. There are no indexes defined on dbo.TestPerformance, so SQL will have no choice but to scan the table in both instances. If there were an index on the table, the second query will be faster. Goes back to having a SARGable WHERE clause.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Read my script again.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Actually, problem with the screen I was using. On my computer I can see it all. Looks like I need to replace some hardware at home.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Will you replace some hardware in my home as well?

This site glitches on my place as well.

_____________
Code for TallyGenerator

ajashadi Ten Centuries Points: 1017 More actions · Answer 9

I did the steps you had mentioned and fired these queries :

select * from dbo.TestPerformance

WHERE convert(datetime,CharDate) >= convert(datetime,getdate())

gave 2992 records in about 5 secs in sql server 2005

select * from dbo.TestPerformance

WHERE DateDate >= getdate() executes instantly and gave 92 records in 0 secs in sql server 2005.

So date to date comparision is efficient. Or I am being stupid here?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Not necessarily. The difference is that the first query has a function being used on the left side of the >= where as the second doesn't. The query engine is not able to use an index as a result of the function so uses a table scan, which reads the entire table, where aas the second query can use an index to more efficently access the data.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Both queries do date comparison.

Just don't apply functions to columns in WHERE clause.

_____________
Code for TallyGenerator