Data Conversion Woes

Or “I’m filtering on ISDATE(dt) = 1. Why am I still getting conversion errors?”

Well, to put it simply, because SQL’s evaluating the where clause in a different order to what you may have expected.

Let’s try a simple example.

SELECT * FROM tbl_TestingConversion
WHERE ISDate(TheDate)=1
 AND CAST(TheDate AS DATETIME) > '2007/07/01'

That query looks like it should work. The filter on ISDate removes the non-date values and then the cast converts the field to datetime for the conversion. However…

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string.

Why is it throwing an error? A quick look at the execution plan shows the following.

The optimiser has picked a plan that does the conversion first, and the ISDate check later. The cast is running on all the rows in the table, and failing on the first one that can’t be converted to a date

So, how do we fix this? The simplest solution is to always use proper data types. Put dates in datetime columns, numbers in int, bigint, numeric, float or whatever is applicable and strings in char and varchar. Unfortunately life isn’t always that simple.

There’s no easy way to get SQL to run the query without ever failing. I’ll take a look at a few options in the next post.

———————————-

Sample table structure:

create table tbl_TestingConversion (
 id int identity primary key,
 TheDate varchar(20),
 SomeField varchar(30)
)

insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/01/01','January')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/02/01','February')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/03/01','March')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/04/01','April')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/05/01','May')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/06/01','June')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('Oops','*shrug*')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/07/01','July')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/08/01','August')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('2007/09/01','September')
insert into tbl_TestingConversion (TheDate, SomeField)
values ('Not a date','Who knows?')
GO