Geek Answers Handbook

SQL The fastest way to compare two dates (custom format varchar and datetime)

I have two date fields that I need to join.

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

The second is varchar (8) in child format with red voice mmddyyyy

Now it becomes painful because there is no easy way to convert to the appropriate type. There is a built-in yyyymmdd format, but not matching the varchar format.

There are two ways that 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

What's 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
+4
source share
4 answers

Is this 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)
+5
source

Try the following:

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

Update

alt text

+2
source

Another approach is as follows:

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

one more thing: a more accurate comparison of real execution costs than relying on optimizer estimates.

+2
source

Suggest you either fix the datetime column or add the datetime column to the table and convert the data so that you need to do this conversion once the data has been entered (and, of course, for existing data). This can probably even be a calculated column. This is NOT what you want to do in select statements. If necessary, create a dateconversion table with each date in both formats and join it if the table cannot be modified.

You can also check to make sure there are no invalid dates in it, which is always possible by storing dates in a data type other than date and time.

+2
source

More articles:


All Articles