Hi Wal,
I am not suggesting this is necessarily the best approach, but at least it
forces the expression evaluation order you want:
Select Value
>From DatesTest
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>
