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 > > >
<<hjffbgac.png>>
<<bffffiac.png>>
