Post reply

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

  • 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

  • January 23, 2007 at 11:19 am

    #685010

    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!

  • January 23, 2007 at 11:29 am

    #685016

    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

  • January 23, 2007 at 12:53 pm

    #685037

    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

  • January 23, 2007 at 1:44 pm

    #685048

    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

  • January 23, 2007 at 2:07 pm

    #685052

    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.

     

  • January 23, 2007 at 2:13 pm

    #685053

    Read my script again.

    _____________
    Code for TallyGenerator

  • January 23, 2007 at 2:17 pm

    #685055

    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.

  • January 23, 2007 at 2:20 pm

    #685056

    Will you replace some hardware in my home as well?

    This site glitches on my place as well.

    _____________
    Code for TallyGenerator

  • January 23, 2007 at 2:49 pm

    #685067

    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?

  • January 23, 2007 at 3:29 pm

    #685080

    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.

  • January 23, 2007 at 3:30 pm

    #685081

    Both queries do date comparison.

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

    _____________
    Code for TallyGenerator

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

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