SQL Fastest way to compare two dates (non standard varchar format and datetime)

Question

I have two "date" fields that I need to join on.

The first is a normal datetime in the format yyyy-mm-dd hh:mm:ss

The second is a varchar(8) in the red-headed step child format mmddyyyy

Now this gets painful because there is no easy way to convert to the corresponding type. There is a built-in format that is yyyymmdd but that doesn't match the varchar format.

There are two paths I can see:

declare @normal_date as datetime;
declare @hated_date as varchar(8);

set @normal_date='1974-11-01 00:00:00.000'
set @hated_date='11011974'

--cast to date time with string splits
select @normal_date
where CONVERT(datetime, RIGHT(@hated_date,4)+LEFT(@hated_date,2)+SUBSTRING(@hated_date,3,2))=@normal_date

--convert normal date to ackward format
select @normal_date
      where REPLACE(CONVERT(varchar(10),@normal_date,101), '/','')=@hated_date

Which is better? Or is there a better way?

Edited to show costs

--Operator cost (39%)
CONVERT(datetime, RIGHT(@hated_date,4)+LEFT(@hated_date,2)+SUBSTRING(@hated_date,3,2))=@normal_date

--Operator cost (57%)
REPLACE(CONVERT(varchar(10),@normal_date,101), '/','')=@hated_date

--Operator cost (46%)
cast(stuff(stuff(@hated_date, 3,0, '/'),6,0,'/') as datetime)=@normal_date

--Operator cost (47%)
RIGHT(@hated_date, 4) + LEFT(@hated_date, 4)=@normal_date

Answer 1

Try this:

select cast(stuff(stuff('11011974', 3,0, '/'),6,0,'/') as datetime)

Update

Answer 2

This is yyyymmdd no?

RIGHT(@hated_date, 4) + LEFT(@hated_date, 4)

So, your script becomes

declare @normal_date as datetime;
declare @hated_date as varchar(8);

set @normal_date='1974-11-01 00:00:00.000'
set @hated_date='11011974'

--SELECT @hated_date = RIGHT(@hated_date, 4) + LEFT(@hated_date, 4))

select 'hurrah' WHERE @normal_date = RIGHT(@hated_date, 4) + LEFT(@hated_date, 4)

Answer 3

Another approach is this:

MONTH(@normal_date)*1000000 + DAY(@normal_date)*10000 + YEAR(@normal_date)
=
CAST(@hated_date AS INT)

one more thing: it is more precise to compare real execution costs than to rely on the optimizer's estimates.

Answer 4

Suggest you either fix the column to be datetime or add a datetime column to the table and convert the data so that you only have to do this conversion once when the data is entered (and once of course for existing data) This could probaly even be a calculated column. This is NOT something you want to be doing in select statements. If necessary create a dateconversion table with every opossible date in both formates and join to it if the table can't be changed.

You might also want to check to make sure there are no invalid dates in there which is always a possibility with storing dates in a data type other than a datetime one.