Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-11-12 Thread Piers Williams 
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 persisted, calculated IsDate column
on your table, putting a filtered indexed view on that and selecting out of
the view for the convert, but that seems like a sledgehammer approach.

I think i'd go the CASE route as Mark suggested. That is not fetch order
dependent (as best I'm aware, having slipped up already this thread :)
On 7 Nov 2012 17:57, Wallace Turner wallacetur...@gmail.com wrote:

  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 conversion error:

 select * from
 (
 SELECT Value from DatesTest
 WHERE IsDate([Value])=1
 ) sub
 where CONVERT(DATETIME, sub.Value,6)  GETDATE()


 Cheers

 On 6/11/2012 5:45 PM, Piers Williams wrote:

 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 so forth.

 If the optimiser thinks it can exclude more rows using indexes etc...
 it'll do that first (even if that involves doing the convert) and leave the
 IsDate to the 'residual predicate' (ie afterwards). That's the problem you
 are seeing. Your where clauses can be resolved in any order.

 Actually even your working case can fail too. I've hit this loads of time
 converting numbers tables to date ranges.

 Always go the sub query if there's a convert and not all the input data is
 valid for it.
 On 29 Oct 2012 15:35, Wallace Turner wallacetur...@gmail.com 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




image/pngimage/pngattachment: image/pngattachment: image/png

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-11-08 Thread Mark Hurd 
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, Value, 6) ELSE NULL END AS Value
   FROM DatesTest
 --  WHERE IsDate([Value])=1
)
select * from sub
where sub.Value  GETDATE()

-- 
Regards,
Mark Hurd, B.Sc.(Ma.)(Hons.)

On 7 November 2012 20:26, Wallace Turner wallacetur...@gmail.com wrote:

  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 conversion error:

 select * from
 (
 SELECT Value from DatesTest
 WHERE IsDate([Value])=1
 ) sub
 where CONVERT(DATETIME, sub.Value,6)  GETDATE()


 Cheers


 On 6/11/2012 5:45 PM, Piers Williams wrote:

 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 so forth.

 If the optimiser thinks it can exclude more rows using indexes etc...
 it'll do that first (even if that involves doing the convert) and leave the
 IsDate to the 'residual predicate' (ie afterwards). That's the problem you
 are seeing. Your where clauses can be resolved in any order.

 Actually even your working case can fail too. I've hit this loads of time
 converting numbers tables to date ranges.

 Always go the sub query if there's a convert and not all the input data is
 valid for it.
 On 29 Oct 2012 15:35, Wallace Turner wallacetur...@gmail.com 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




image/pngimage/png

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-11-07 Thread Wallace Turner 

  
  
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 conversion error:
  
  select * from 
( 
SELECT Value from DatesTest 
WHERE IsDate([Value])=1
) sub
where CONVERT(DATETIME, sub.Value,6)  GETDATE()

  
  Cheers
  
  On 6/11/2012 5:45 PM, Piers Williams wrote:


  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 so forth.
  If the optimiser thinks it can exclude more rows
using indexes etc... it'll do that first (even if that involves
doing the convert) and leave the IsDate to the 'residual
predicate' (ie afterwards). That's the problem you are seeing.
Your where clauses can be resolved in any order. 
  Actually even your working case can fail too. I've
hit this loads of time converting numbers tables to date ranges.
  Always go the sub query if there's a convert and not
all the input data is valid for it.
  On 29 Oct 2012 15:35, "Wallace Turner"
wallacetur...@gmail.com
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

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-11-06 Thread Piers Williams 
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
so forth.

If the optimiser thinks it can exclude more rows using indexes etc... it'll
do that first (even if that involves doing the convert) and leave the
IsDate to the 'residual predicate' (ie afterwards). That's the problem you
are seeing. Your where clauses can be resolved in any order.

Actually even your working case can fail too. I've hit this loads of time
converting numbers tables to date ranges.

Always go the sub query if there's a convert and not all the input data is
valid for it.
On 29 Oct 2012 15:35, Wallace Turner wallacetur...@gmail.com 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



hjffbgac.pngbiac.png

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Emily Waghorne 
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:



 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



biac.pnghjffbgac.png

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Les Hughes 

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

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Davy Jones 
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:

 biac.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

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Les Hughes 

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

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Davy Jones 
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

RE: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Williams, Thomas 
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

Re: [OT] sql convert datetime problem; forcing order of AND statements

2012-10-29 Thread Wallace Turner 
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