Date Comparison Vs String Comparison

  • @SQLApprentice

    SSC Eights!

    Points: 950

    I have 2 tables in Test DB.

    The 1st table has a varchar(23) column which stores the date in string format. The 2nd table has a datetime coulmn, which stores the date in datetime format.

    Question:

    Considering performance, is it better to Convert the varchar cloumn to datetime format to compare dates between Table 1 and Table 2

    OR

    is it better to Convert the datetime coumn in the 2nd table to string format and comapre the dates that way!

  • Jack Corbett

    SSC Guru

    Points: 184360

    Neither is very good as doing any conversion on a column will at best use an index scan. The best solution is to store data in a properly typed column.

    There are other problems with both. With converting the date to a string you need to make sure that they are both in the same format and how can you guarantee that the string date column has a consistent format. When converting the string date column to a date you may get an "invalid date" error and the entire batch will fail because you can't guarantee that you will get a value that evaluates to an acceptable date.

    All that being said, if I had to do it, I'd probably convert the date to string to avoid the batch failing. You won't be guaranteed that you will get the correct data returned, but you will get something.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Comparing as a string will only work if the varchar date is in the same format in all rows.

    The code below should work OK even with invalid dates in the varchar date column.

    select

    a.*,

    b.*

    from

    MyDatetimeDates a

    join

    MyStringDates b

    on

    a.DTDate =

    convert(datetime,case when isnull(isdate(b.MyStringDate),0) 1 then null else b.MyStringDate end)

  • sgmunson

    SSC Guru

    Points: 110433

    Ummm... I think the real problem depends on the nature of the comparison. Looking for dates within x number of days of each other is NOT something you're going to want to attempt by converting to string format, regardless of how consistent you are in making the conversion. On the other hand, if one is only seeking to establish that two dates are either equal or they're not, then conversion to string can work. Unfortunately, almost every comparison OTHER than mere equality (or lack thereof), has problems. Every string-based datetime value comparison requires that the date/time value be presented in Y M D HH:MM:SS order, whereas datetime values can be directly compared without concern over formatting. Any need to compare based on the actual date/time difference between two values should be done by conversion to datetime values.

    Steve

    (aka smunson)

    :-):-):-)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Jack Corbett

    SSC Guru

    Points: 184360

    Excellent point Steve. Another reason why you need to use the correct data type for the column in the first place.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

Viewing 5 posts - 1 through 5 (of 5 total)

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