This works for me, but I don't truly know if it is still really dependent upon evaluation order, although I'd hope not. Note the Where IsDate clause is not needed if you want to consider non-date values as NULLs.
With sub AS (SELECT Value AS "Text", CASE WHEN IsDate(Value)=1 THEN
CONVERT(DATETIME, Value, 6) ELSE NULL END AS "Value"
FROM DatesTest
-- WHERE IsDate([Value])=1
)
select * from sub
where sub.Value > GETDATE()
--
Regards,
Mark Hurd, B.Sc.(Ma.)(Hons.)
On 7 November 2012 20:26, Wallace Turner <wallacetur...@gmail.com> wrote:
> Thank you for responding; what I'm taking away from what you said is:
>
> >Always go the sub query if there's a convert and not all the input data
> is valid for it.
>
> Perhaps you can edumacate me: I'm trying the following query but *still*
> getting the conversion error:
>
> select * from
> (
> SELECT Value from DatesTest
> WHERE IsDate([Value])=1
> ) sub
> where CONVERT(DATETIME, sub.Value,6) > GETDATE()
>
>
> Cheers
>
>
> On 6/11/2012 5:45 PM, Piers Williams wrote:
>
> Sorry to see this late, but I think the answers are a bit incomplete.
>
> As other have said, you should use a sub query (or cte) to force it in
> this type of circumstances. Unless you do, the order that the convert and
> where run are determined by the query plan, so depend on indexes,
> statistics and so forth.
>
> If the optimiser thinks it can exclude more rows using indexes etc...
> it'll do that first (even if that involves doing the convert) and leave the
> IsDate to the 'residual predicate' (ie afterwards). That's the problem you
> are seeing. Your where clauses can be resolved in any order.
>
> Actually even your working case can fail too. I've hit this loads of time
> converting numbers tables to date ranges.
>
> Always go the sub query if there's a convert and not all the input data is
> valid for it.
> On 29 Oct 2012 15:35, "Wallace Turner" <wallacetur...@gmail.com> wrote:
>
>> I'm running into an issue with a select query; it appears the CONVERT
>> operator is performed before any other condition in the WHERE clause.
>>
>> Consider the data below:
>>
>>
>>
>> Now some queries,
>> This one works, note only 6 rows are returned:
>>
>> SELECT Value,CONVERT(DATETIME, [Value],6) from DatesTest
>> WHERE
>> IsDate([Value])=1
>>
>>
>>
>> This one does *not *work: Conversion failed when converting date and/or
>> time from character string.
>>
>> SELECT Value from DatesTest
>> WHERE
>> IsDate([Value])=1
>> AND CONVERT(DATETIME, [Value],6) > GETDATE()
>>
>>
>> 1) Why is the CONVERT statement being executed first?
>> 2) How can the IsDate be forced to execute first so the second statement
>> works?
>>
>> Cheers
>>
>> Wal
>>
>>
>>
>
<<image/png>>
<<image/png>>
