August 19, 2009 at 10:09 am
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!
August 20, 2009 at 7:22 am
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
Check 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 help
Need an Answer? Actually, No ... You Need a Question
August 20, 2009 at 7:55 am
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)
August 21, 2009 at 1:49 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2009 at 1:58 pm
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
Check 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 help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply