Re: [OT] sql convert datetime problem; forcing order of AND statements

[Wallace Turner]

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