Oh dear. Apparently I still don't really understand it then. Presumably the optimizer can collapse the subquery in this case (because the WHERE restrictions are not on the same column), and *still* end up running the predicates the 'wrong' way round.
You could force it for sure by having a persisted, calculated IsDate column on your table, putting a filtered indexed view on that and selecting out of the view for the convert, but that seems like a sledgehammer approach. I think i'd go the CASE route as Mark suggested. That is not fetch order dependent (as best I'm aware, having slipped up already this thread :) On 7 Nov 2012 17:57, "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>>
<<attachment: image/png>>
<<attachment: image/png>>
