SQL DateTime Conversion Fails when No Conversion Should be Taking Place

Question

I'm modifying an existing query for a client, and I've encountered a somewhat baffling issue.

Our client uses SQL Server 2008 R2 and the database in question provides the user the ability to specify custom fields for one of its tables by making use of an EAV structure. All of the values stored in this structure are varchar(255), and several of the fields are intended to store dates. The query in question is being modified to use two of these fields and compare them (one is a start, the other is an end) against the current date to determine which row is "current".

The issue I'm having is that part of the query does a CONVERT(DateTime, eav.Value) in order to turn the varchar into a DateTime. The conversions themselves all succedd and I can include the value as part of the SELECT clause, but part of the question is giving me a conversion error:

Conversion failed when converting date and/or time from character string.

The real kicker is this: if I define the base for this query (getting a list of entities with the two custom field values flattened into a single row) as a view and select against the view and filter the view by getdate(), then it works correctly, but it fails if I add a join to a second table using one of the (non-date) fields from the view. I realize that this might be somewhat hard to follow, so I can post an example query if desired, but this question is already getting a little long.

I've tried recreating the basic structure in another database and including sample data, but the new database behaves as expected, so I'm at a loss here.

EDIT In case it's useful, here's the statement for the view:

create view Festival as 
select
    e.EntityId as FestivalId,
    e.LookupAs as FestivalName,
    convert(Date, nvs.Value) as ActivityStart,
    convert(Date, nve.Value) as ActivityEnd

from tblEntity e

left join CustomControl ccs on ccs.ShortName = 'Activity Start Date'
left join CustomControl cce on cce.ShortName = 'Activity End Date'
left join tblEntityNameValue nvs on nvs.CustomControlId = ccs.IdCustomControl and nvs.EntityId = e.EntityId
left join tblEntityNameValue nve on nve.CustomControlId = cce.IdCustomControl and nve.EntityId = e.EntityId

where e.EntityType = 'Festival'

The failing query is this:

select * 

from Festival f

join FestivalAttendeeAll fa on fa.FestivalId = f.FestivalId

where getdate() between f.ActivityStart and f.ActivityEnd

Yet this works:

select * 

from Festival f

where getdate() between f.ActivityStart and f.ActivityEnd

(EntityId/FestivalId are int columns)

Answer 1

I've encountered this type of error before, it's due to the "order of operations" performed by the execution plan.

You are getting that error message because the execution plan for your statement (generated by the optimizer) is performing the CONVERT() operation on rows that contain string values that can't be converted to DATETIME.

Basically, you do not have control over which rows the optimizer performs that conversion on. You know that you only need that conversion done on certain rows, and you have predicates (WHERE or ON clauses) that exclude those rows (limit the rows to those that need the conversion), but your execution plan is performing the CONVERT() operation on rows BEFORE those rows are excluded.

(For example, the optimizer may be electing to a do a table scan, and performing that conversion on every row, before any predicate is being applied.)

I can't give a specific answer, without a specific question and specific SQL that is generating the error.

---

One simple approach to addressing the problem would be to use the ISDATE() function to test whether the string value can be converted to a date.

That is, replace:

CONVERT(DATETIME,eav.Value)

with:

CASE WHEN ISDATE(eav.Value) > 0 THEN CONVERT(DATETIME, eav.Value) ELSE NULL END

or:

CONVERT(DATETIME, CASE WHEN ISDATE(eav.Value) > 0 THEN eav.Value ELSE NULL END)

Note that the ISDATE() function is subject to some significant limitations, such as being affected by the DATEFORMAT and LANGUAGE settings of the session.

---

If there is some other indication on the eav row, you could use some other test, to conditionally perform the conversion.

CASE WHEN eav.ValueIsDateTime=1 THEN CONVERT(DATETIME, eav.Value) ELSE NULL END

---

The other approach I've used is to try to gain some modicum of control over the order of operations of the optimizer, using inline views or Common Table Expressions, with operations that force the optimizer to materialize them and apply predicates, so that happens BEFORE any conversion in the outer query.