Hi SQL always executes right to left, all parts of your query are executed but the filter is only applied on your results. However, the sqlserver optimiser can and will rearrange your query for you so even checking to see if it is a date first ( to the right ) is not guaranteed to work. So you need a case when to do your processing.
Davy Sent via telegraph. On 29 Oct 2012, at 08:46, Emily Waghorne <ozdot...@emigram.com> wrote: Hi Wal, I'm not sure it's a case of one being executed first, because I assume the where clause has to be executed in its entirety before the engine decides if it matches or not. You are thinking like the && operator in c# which only executes the second half of the comparison if the first half passes first. So basically you have to write it so that it filters on IsDate and only then you run your convert. The best way might depend on the volume if data you have. Sub query? CTE? Emily On Monday, 29 October 2012, Wallace Turner 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: > > <bffffiac.png> > > Now some queries, > This one works, note only 6 rows are returned: > > SELECT Value,CONVERT(DATETIME, [Value],6) from DatesTest > WHERE > IsDate([Value])=1 > > <hjffbgac.png> > > 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 > > >
