As an alternative to CTE/subselect, you could do something like below. Not sure
of the implications on indexing:
--temp table
DECLARE @temp TABLE (
[Value] VARCHAR(15)
)
--populate
INSERT INTO @temp
SELECT '28-Nov-2012' UNION ALL
SELECT '28-Nov-2012' UNION ALL
SELECT 'blah'
SELECT [Value]
FROM @temp
WHERE --only convert if date - comparisons to NULL return NULL
CONVERT(DATE, CASE WHEN ISDATE([Value]) = 1 THEN [Value] ELSE NULL END)
> GETDATE()
Thomas
-----Original Message-----
From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] 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 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 does
Id =3
Date > 1997
Select *
The example provided I have no idea which the optimiser thinks is more
performant, the point being you can't write standard logic in SQL, all parts
are executed. A select case is a special construct to get around the problems.
I would not like to do the convert date on a string of characters over and over
any way. Are all these dates stored in the same way?
Davy
Sent via telegraph.
On 29 Oct 2012, at 10:25, Les Hughes <l...@datarev.com.au> wrote:
> 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
Peninsula Health - Metropolitan Health Service of the Year 2007 & 2009
