[OT] sql convert datetime problem; forcing order of AND statements
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
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
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
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
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
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
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
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
Creating Manuals/User Guides
Hi, Anyone has a good way of creating manuals/training/user guides? I've found HTML/Word very cumbersome and more or less useless so I'd like something better. There is also a service (www.dozuki.com) that is the basis of iFixIt website that looks great and they use an open source format called omanual (http://omanual.com/) however there seem to be no parsers or content-creators that work with omanual except the one from dozuki and I don't feel like paying $150 a month for a service to host few user guides. Thoughts? Thanks, Corneliu.
Re: Creating Manuals/User Guides
I haven't tried it yet but we use a suite of tools for acceptance testing that are capable of automating the user interface and taking screenshots of various windows when test steps go wrong. I want to write a collection of Documentation Tests that when run produce a report that shows key scenarios end to end and takes a screenshot for each step as the basis for a visual walkthrough of the system. These tools are based off of Ranorex which is a commercial automation suite but you could probably achieve similar results with Project White or WatiN. On Tue, Oct 30, 2012 at 9:41 AM, Corneliu I. Tusnea corne...@acorns.com.auwrote: Hi, Anyone has a good way of creating manuals/training/user guides? I've found HTML/Word very cumbersome and more or less useless so I'd like something better. There is also a service (www.dozuki.com) that is the basis of iFixIt website that looks great and they use an open source format called omanual (http://omanual.com/) however there seem to be no parsers or content-creators that work with omanual except the one from dozuki and I don't feel like paying $150 a month for a service to host few user guides. Thoughts? Thanks, Corneliu.
Re: Creating Manuals/User Guides
DrExplain On 30/10/2012 12:41 PM, Corneliu I. Tusnea wrote: Hi, Anyone has a good way of creating manuals/training/user guides? I've found HTML/Word very cumbersome and more or less useless so I'd like something "better". There is also a service (www.dozuki.com) that is the basis of iFixIt website that looks great and they use an "open source" format called omanual (http://omanual.com/) however there seem to be no parsers or content-creators that work with omanual except the one from dozuki and I don't feel like paying $150 a month for a service to host few user guides. Thoughts? Thanks, Corneliu.
