Hi, I wasn't getting these responses at first so apologies for the delay
in responding.
In between then and now I ended up going with the CASE solution (same as
Les and Thomas, thank you)
I'm going to stick with it but I'm not a huge fan of that as its clearly
then doing unnecessary work by calling `IsDate` on columns it doesnt
need to.
/
>Short answer is: SQL does short-circuit based on a mysterious
tarot-card engine it has internally. It will not reveal its hidden
secrets. /
:)
On 29/10/2012 5:27 PM, Fredericks, Chris wrote:
Hi Wal,
I am not suggesting this is necessarily the best approach, but at
least it forces the expression evaluation order you want:
SelectValue
FromDatesTest
Where 1 = Case
When IsDate(Value) = 1
Then Case
When Cast(Value As datetime) > GetDate()
Then 1
Else
0
End
Else
0
End;
Cheers,
Chris
-----Original Message-----
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 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
Hi Wal,
Short answer is: SQL does short-circuit based on a mysterious
tarot-card engine it has internally. It will not reveal its hidden
secrets.
Check this for more info:
http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx
It also links to here which shows a good illustration:
http://beingmarkcohen.com/?p=62
A CTE or the workarounds on the listed URLs are the way to go.
Best of luck :)
--
Les Hughes
l...@datarev.com.au <mailto:l...@datarev.com.au>
