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
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,
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
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
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
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:
Now some queries,
This one works, note only 6 rows are returned:
|SELECT Value,CONVERT(DATETIME,
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
Davy Jones wrote:
Hi
SQL always executes right to left
Are you talking about boolean evauluation? If so... False. (At least for
2008 R2 which I have in front of me)
--
Les Hughes
l...@datarev.com.au
As far as I am aware, and coming from a time when we didn't have
optimisers. All parts are evaluated
A part, in the case of a case else is is the statement as a whole.
Select * from t where Id = 3 and date 1997
Without the optimiser, It does
Date 1997
Id = 3
Select *
With the optimiser it
] On
Behalf Of Davy Jones
Sent: Monday, 29 October 2012 11:47 PM
To: ozDotNet
Subject: Re: [OT] sql convert datetime problem; forcing order of AND statements
As far as I am aware, and coming from a time when we didn't have optimisers.
All parts are evaluated
A part, in the case of a case else
-
From: ozdotnet-boun...@ozdotnet.com
[mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Les Hughes
Sent: Monday, 29 October 2012 6:58 PM
To: ozDotNet
Subject: Re: [OT] sql convert datetime problem; forcing order of AND
statements
Wallace Turner wrote:
I'm running into an issue with a select
11 matches
Mail list logo
