Convert string to datetime in where statement in SQL Server

Question

I have a problem with string to datetime conversion in SQL Server 2005.

If use Convert(datetime, x) in a where statement like this:

SELECT ThumbId
FROM [ImageBankSQL].[dbo].[MetaData]
WHERE (DataKey = 'ImagePropertyTagExifDTOrig'
       OR DataKey='ImagePropertyTagDateTime')
AND CONVERT(datetime, DataValue, 120) > CONVERT(datetime, '2011-09-23 00:00:00', 120)

I get:

Conversion failed when converting datetime from character string

But if I select the converted datetime instead of using them in the where statement, everything is fine, and I get my converted dates:

SELECT ThumbId, Convert(datetime, DataValue, 120), Convert(datetime, '2011-09-23 00:00:00', 120)
FROM [ImageBankSQL].[dbo].[MetaData]
WHERE (DataKey = 'ImagePropertyTagExifDTOrig'
       OR DataKey = 'ImagePropertyTagDateTime')

In both examples I have used both Convert(datetime, DataValue, 120) and Convert(datetime, DataValue) with the same results.

Why can I convert in select, but not in where? And how would I make my first query with convert in the where statement work?

Please see http://stackoverflow.com/questions/7263501/conversion-to-datetime-fails-only-on-where-clause. — Daniel Renshaw, May 23 '12 at 11:56

score 0 · Accepted Answer · answered May 23 '12 at 11:58

When you do the select, the conversion is only applied to the DataValue that meet your criteria (i.e. DataKey = 'ImagePRopertyTagDateTime')

SQL Server does not perform lazy evaluation, so with the Convert in the where clause, it will be evaluated against all rows, even when the DataValue is not suitable for parsing as a date.

Convert string to datetime in where statement in SQL Server

1 Answers1