locked
Order of precedence in where clause RRS feed

  • Question

  • Im getting an error on this statement:

    SELECT *

    FROM

    [My Table]

    WHERE

    ISDATE([DATE OF BIRTH]) = 1

    AND

    CONVERT(DATETIME, [DATE OF BIRTH], 101) < '01/01/1899'

    Because its evaluating the second condition first and running into non dates.  Is there a way to change this where clause to guarantee it will exclude the non-dates? Also, because of the way the app was written with dynamic SQL, it has to be done in the where clause.

    the error is :

    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


    John Schroeder



    • Edited by jschroeder Thursday, December 19, 2013 5:17 PM
    Thursday, December 19, 2013 5:15 PM

Answers

  • The optimiser can change the order of execution.

    try

    where case when isdate([date of birth]) = 1 then CONVERT(DATETIME, [DATE OF BIRTH], 101) else '18990101' end < '18990101'

    That's if you can rely on isdate to guarantee a valid date conversion.

    • Marked as answer by jschroeder Thursday, December 26, 2013 3:04 PM
    Thursday, December 19, 2013 5:19 PM

All replies

  • Yes.

    Make the ISDATE condition a subquery or CTE.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by jschroeder Thursday, December 19, 2013 5:22 PM
    • Unmarked as answer by TiborKMVP Thursday, December 19, 2013 7:09 PM
    • Proposed as answer by HimanshuSharma Thursday, February 13, 2014 5:27 AM
    • Unproposed as answer by HimanshuSharma Thursday, February 13, 2014 5:28 AM
    Thursday, December 19, 2013 5:18 PM
  • The optimiser can change the order of execution.

    try

    where case when isdate([date of birth]) = 1 then CONVERT(DATETIME, [DATE OF BIRTH], 101) else '18990101' end < '18990101'

    That's if you can rely on isdate to guarantee a valid date conversion.

    • Marked as answer by jschroeder Thursday, December 26, 2013 3:04 PM
    Thursday, December 19, 2013 5:19 PM
  • Thanks

    John Schroeder

    Thursday, December 19, 2013 5:23 PM
  • You can't control the order of evaluation.

    In SQL 2012, you can use TRY_CONVERT.

    http://technet.microsoft.com/en-us/library/hh230993.aspx

    • Proposed as answer by Naomi N Thursday, December 19, 2013 5:49 PM
    Thursday, December 19, 2013 5:29 PM
  • I also don't think that subquery will actually help. The optimizer can still re-arrange execution. Only if you subselect into a temp table first.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 5:50 PM
  • You may want to read the second article mentioned in this blog post

    http://blogs.technet.com/b/wikininjas/archive/2013/12/08/sunday-surprise-let-s-catch-the-errors.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 5:54 PM
  • I'm sorry, but a subquery or CTE is not the answer here. The optimizer can, at will, do "predicate pushing" (Bingle it for more info) and push the predicate of the outer query into the inner query and you are back to the exact same execution plan as you began with.

    Use Naomi's suggestion to materialize the "safe" rows into a table variable or temp table first and then query that from a second query, or go with Nigel's suggestion (a CASE construct). Your tests will tell you which performs best in your particular situation.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Naomi N Thursday, December 19, 2013 6:47 PM
    • Unproposed as answer by Pan Zhang Tuesday, December 24, 2013 8:26 AM
    • Proposed as answer by Pan Zhang Tuesday, December 24, 2013 8:26 AM
    Thursday, December 19, 2013 6:40 PM
  • BTW, I tried unmarking Kalman's reply as an answer and it didn't work for me. I have a bug reported in forum's issues because lately I am unable to unpropose or unmark answers. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 6:59 PM

  • Have you considered writing SQL and not dialect? The first thing you missed is that we do not embed blanks in data element names; read ISO-11179 or any book on data modeling. The only date display format allowed in ANSI/ISO Standard SQL is ISO-8601, which is “yyyy-mmn-dd”; it is one of the most common ISO standards in IT, as it is embedded in so many other standards. 

    We do not use the 1970's Sybase string function CONVERT; we have a proper DATE data type now, so dates are not COBOL strings any more! The ANSI/ISO Standard “CAST (birth_date AS DATE)” will do the conversion, if your data is screwed up. The DATE data type has a range of '0001-01-01' to '9999-12-31', which should be enough for anyone but a museum or an archeologist. Oh, it is also smaller than the old proprietary Sybase DATETIME data type. 

    We do not use things like ISDATE() in a correctly build system; we know the data type of the column and have done the data scrubbing with ETL tools or a presentation layer. Do you understand the concept of a tiered architecture? The data in the tables is supposed to be clean. 

    What you are getting are kludges and not real help. Your approach is to write 1970's COBOL style code in 1970's Sybase SQL. This will cost you performance, maintainability, portability and the cool kids will laugh at you.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 19, 2013 7:26 PM

  • The only date display format allowed in ANSI/ISO Standard SQL is ISO-8601, which is “yyyy-mmn-dd”; it is one of the most common ISO standards in IT, as it is embedded in so many other standards. 

    'yyyy-mm-dd' is NOT the correct choice for SQL server though

    Have a read of this extremely helpful article: http://www.karaszi.com/sqlserver/info_datetime.asp (in particular the Warnings and common misconceptions section)

    • Proposed as answer by Naomi N Thursday, December 19, 2013 7:55 PM
    • Unproposed as answer by Pan Zhang Tuesday, December 24, 2013 8:25 AM
    • Proposed as answer by Pan Zhang Tuesday, December 24, 2013 8:26 AM
    Thursday, December 19, 2013 7:53 PM
  • Exactly. I was meaning to point this out as well.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 7:56 PM
  • Celko,

    |dates are not COBOL strings any more

    As a matter of fact, yes the date is a COBAL string.  It's being imported from a COBAL system.

    This link may help you http://en.wikipedia.org/wiki/Pedant

    Also, since I inherited and did not write the app, my options are limited. 

    |The first thing you missed is that we do not embed blanks in data element names

    The field name is from a COBAL system written decades ago.

    | the cool kids will laugh at you. 

    I am a cool kid, unlike you.  And I'm the one laughing.


    John Schroeder



    • Edited by jschroeder Thursday, December 19, 2013 10:30 PM
    Thursday, December 19, 2013 10:21 PM
  • I cannot rely on isdate to guarantee a valid date conversion. 

    John Schroeder

    Thursday, December 19, 2013 11:27 PM
  • ISDATE will tell you if the value can be converted into a date using one of the conversion styles. If you have mixed styles saved in the database, then you're in trouble.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 11:40 PM
  • Not using standards is never right. Microsoft is moving to ANSI/ISO Standard temporal data. This means that Nobody should be using the old 1970's Sybase DATETIME data types. The CAST() and future temporal features are all going to be based on the standards, with the old stuff hanging on as a “family curse” that will have to be kludged constantly. Not portable, not maintainable, not readable to new programmers that did not grow up with 1970's Sybase SQL. Hardly what I would call a best choice. 

    The bad news is that we are stuck with DATETIME2 instead of the correct TIMESTAMP. Just like Oracle and its VARCHAR2() from their initial screwup. 

    All of these statements return '2003-02-28', as per ANSI/ISO. 

    SET LANGUAGE us_english 
    SELECT CAST('2003-02-28' AS DATETIME2);

    SET LANGUAGE british 
    SELECT CAST('2003-02-28' AS DATETIME2);

    SET LANGUAGE us_english 
    SELECT CAST('02/28/2003' AS DATETIME2);

    SET LANGUAGE british 
    SELECT CAST('28/02/2003' AS DATETIME2);

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 20, 2013 2:41 AM
  • Let me state the obvious, that the best thing to do, is to change the data type of "DATE OF BIRTH" to date. When unknown, store NULL.

    Then, when filtering on "DATE OF BIRTH", compare against a safe date format, which would be 'YYYYMMDD'.

    This and that would lead to:

    SELECT my_column
    FROM   "My Table"
    WHERE  "DATE OF BIRTH" < '18990101'
    

    If you need the query to actually clean up your table, then just take it step by step, and use temporary tables to process the rows and clean the values.


    Gert-Jan

    • Proposed as answer by Naomi N Friday, December 20, 2013 3:51 PM
    Friday, December 20, 2013 3:28 PM
  • Not using standards is never right.

    Are you sugesting I rebuild the entire application I've inhereted?  I don't think that's in the company budget for this year. "Have you considered writing" useful replies instead of making normative judgments? Oh, and using this forum as a vehicle for self promotion is "never right" either.


    John Schroeder



    • Edited by jschroeder Friday, December 20, 2013 4:03 PM
    Friday, December 20, 2013 3:38 PM
  • Are you suggesting I rebuild the entire application I've inherited?” is classic! Let’s close our eyes and just kludge it (with help from SQL forums) until it falls apart with deprecation. You will have switched job by then anyway, right?

    This sounds like you inherited a classic “family curse” application written in SQL Server 2000 or earlier and never given adaptive maintenance as releases came out. Say that in your positing! I am suggesting that you begin a policy of SEI style professionalism. Do they still teach software maintenance as part of Software Engineering?

    Corrective: maintenance performed to correct faults in hardware or software. Oh, you meant (x >= 0) and and not (x > 0) and so forth. This one explains itself and it is what most people think of when they hear the words “software maintenance”; it is not the whole picture.

    Adaptive: software maintenance performed to make a computer program usable in a changed

    environment. Us database guys have it a little easier because in a tiered environment, much of this is done in the presentation layers. Our job is to throw data “over the wall” in industry standard format, so that any display formatting, special computations, etc can be done by someone else. But if the environment changes, as when European countries switched to the Euro or US retail went to GTIN from UPC, then we have to change our tables.

    We also have to re-write code when features are deprecated. Anyone want ot try to use *= in place of LEFT OUTER JOIN?

    Perfective: software maintenance performed to improve the performance, maintainability, or other

    attributes of a computer program. Functionality is the same. In a procedural language, we might replace a Bubble Sort with a QuickSort.. Better queries and better indexes are part of how we do it in SQL.

    Preventative: maintenance performed for the purpose of preventing problems before they occur. This is the red-haired stepchild of software maintenance. Have you worked with people who like disaster? They love to run in and save the day, be a hero. But they could have done something when it was easy and never had the problem.

    In SQL, we have constraints that can prevent bad data. How many of your INTEGER columns in your schema have CHECK (x >= 0) or CHECK (x > 0) constraints to help the optimizer? This should cost you a simple ALTER TABLE.

    If you know how, you can set up changes with a maintenance program and get the system corrected. 

    Also why do you think that professionalism and standards are "self-promotion"?  I promise that I get no kick-back from ISO when you read a standard :) 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 20, 2013 6:38 PM
  • Have you considered writing SQL and not dialect?

    Have you?



    John Schroeder

    Friday, December 20, 2013 7:39 PM
  • LOL!  very instructive.  John, I've had this issue multiple time  the solution proposed by Nigel is the one which worked for me all the time.

    Statements inside a case are executed sequentially.

    "The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied."

    You must however make sure you do not put potentially erroneous statements in the when:

    "In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression."

    Take a look at the remark section of this article:  http://technet.microsoft.com/en-us/library/ms181765.aspx

    In other words, if your date manipulations are inside the then statements and you tested the format in the when statements then you should be OK.

    • Edited by Antoine F Friday, December 20, 2013 10:30 PM
    Friday, December 20, 2013 10:23 PM
  • ANSI/ISO Standard SQL is all I write until I am cornered by a SQL which is not up to speed yet. And even then I put the correct code in a comment, so that the poor bastards who come after me can uncomment and test with the next release. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 22, 2013 10:29 PM
  • >> As a matter of fact, yes the date is a COBAL [sic] string.  It's being imported from a COBAL [sic] system. <<

    You misspelled the name of the language :) 

    >> Also, since I inherited and did not write the app, my options are limited. <<

    The options are much better than they used to be. There are ETL tools, etc. But if all you have is COBOL, it is really good at string manipulations. 

    >> { we do not embed blanks in data element names } The field name is from a COBAL [sic] system written decades ago.<<

    COBOL uses dashes where SQL uses underscores. That could be a bulk text edit if you need to keep pre-ISO-11179 names.  But there is no excuse for not having a DATE column to which to map the COBOL field. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 22, 2013 10:37 PM
  • CASE expression, not statement! 

    This was trickier than people first think. When we were voting on this, we found that we had to evaluate all of the THEN and ELSE clauses to determine the data type of the expression. But lead to problems with levels of aggregation and forces the THEN and ELSE clauses to be at the same level. ARRGH! 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 22, 2013 10:44 PM
  • The only date display format allowed in ANSI/ISO Standard SQL is ISO-8601, which is “yyyy-mmn-dd”; it is one of the most common ISO standards in IT

    SET LANGUAGE british
    SELECT CAST('2013-05-21' AS DATETIME)
    

    returns:

    Msg 242, Level 16, State 3, Line 2

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Contrary to popular belief, YYYY-MM-DD for DATETIME variables is NOT language-/dateformat-independent in SQL Server! :)

    yyyymmdd is the best format  in SQL Server.

    Thank you for correcting my spelling. 


    John Schroeder


    • Edited by jschroeder Monday, December 23, 2013 11:12 PM
    Monday, December 23, 2013 11:03 PM
  • The optimiser can change the order of execution.

    try

    where case when isdate([date of birth]) = 1 then CONVERT(DATETIME, [DATE OF BIRTH], 101) else '18990101' end < '18990101'

    That's if you can rely on isdate to guarantee a valid date conversion.


    Thanks Nigel.  A variation on this works for me.

    John Schroeder

    Thursday, December 26, 2013 3:04 PM
  • 'yyyy-mm-dd' is NOT the correct choice for SQL server though

    Have a read of this extremely helpful article: http://www.karaszi.com/sqlserver/info_datetime.asp (in particular the Warnings and common misconceptions section)

    Instead of referring to an article it is better to state your point.

    Why is 'YYYY-MM-DD' ANSI date string not good for SQL Server?

    I am looking for a proof not hearsay. After all we are doing Computer Science, aren't we?

    Tibor stated the same in another thread (without explanation), but I don't find an explanation in the referenced article.

    'YYYYMMDD' & 'YYYY-MM-DD' are both valid ANSI date strings.

    Hi Tibor,

    >I'm sorry, but a subquery or CTE is not the answer here.

    Can you provide a proof?  Thanks.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012





    • Edited by Kalman Toth Thursday, December 26, 2013 3:27 PM
    Thursday, December 26, 2013 3:18 PM
  • Did you check the article I suggested (the article by Saeid Hasani)?

    As for Tibor's blog, it provides a sample of yyyy-mm-dd format failing and I just demonstrated it to you in another thread.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 26, 2013 5:05 PM
  • Hi Naomi,

    Tibor is not Microsoft. The 'YYYY-MM-DD' format is all over BOL and SSMS.

    I would not stop using it because it converts differently in some foreign languages when proper conversion style number is not specified.

    It converts OK in USA.

    Many things are different in date/datetime string representations when dealing with foreign languages, most famous is dmy UK usage as opposed to mdy USA usage.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Edited by Kalman Toth Thursday, December 26, 2013 5:32 PM
    Thursday, December 26, 2013 5:19 PM
  • The point is that if we know that particular format may not convert correctly when converting to datetime, then why keep suggesting that wrong not 100% safe format when there is one 100% safe?

    In my code samples I never use yyyy-mm-dd format answering questions and in our code which I was code reviewing recently I also suggested that safe format to my colleague, so he went ahead and fixed it.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 26, 2013 5:35 PM
  • So why isn't BOL using the "safe" format?

    BOL: http://technet.microsoft.com/en-us/library/ms186724.aspx

    BOL screenshot:


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, December 26, 2013 6:05 PM
  • This is a format used for presentation, not the internal format and not the format that needs to be used for datetime constants that do not have time portion.

    Did you find in BOL the implicit constant of yyyy-mm-dd representing datetime value? And even if you do find it, it doesn't mean BOL shows it correctly.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 26, 2013 6:32 PM
  • This was a question about order precedence.  Why are you blabbering about about what's taught in software engineering classes?  Frankly, myself and a lot of other people wish you would shut the F up.

    John Schroeder

    Wednesday, February 12, 2014 11:49 PM
  • This was a question about order precedence.  Why are you blabbering about about what's taught in software engineering classes?  Frankly, myself and a lot of other people wish you would shut the F up.

    John Schroeder

    The language you are using is not appropriate. CELKO and others are providing their precious time so that we can learn by the heat of debate and discussion. Nobody is just sitting idle and waiting for the questions to be asked here. We all reply whenever we have extra time or we try to find time so that people like you, me and others can benefit from it. Whatever CELKO, Naomi, Kalman, and others have written is what they believe to be true. You have no right to prove them wrong unless you come with the proper explanation.

    I know that CELKO is rude sometimes but he does shed light on important points and I do want to prove him wrong whenever he replies to any thread but I could not so far. And it helps me in the way because for proving him wrong I have to read other articles and Google a lot. That improvises my knowledge on the subject and other related topics too.

    <CELKO : All of these statements return '2003-02-28', as per ANSI/ISO. 

    SET LANGUAGE us_english 
    SELECT CAST('2003-02-28' AS DATETIME2);

    SET LANGUAGE british 
    SELECT CAST('2003-02-28' AS DATETIME2);

    SET LANGUAGE us_english 
    SELECT CAST('02/28/2003' AS DATETIME2);

    SET LANGUAGE british 
    SELECT CAST('28/02/2003' AS DATETIME2); >

    I think you may be wrong because it does not mean if the result set displays in 'YYYY-MM-DD' format, the SQL engine is storing it in the same format. There might be an internal mechanism which the engine is using to store. (But still I am not sure, Its what I think.)

    <CELKO: The only date display format allowed in ANSI/ISO Standard SQL is ISO-8601,>

    This is clearly correct in terms of ISO-8601, but you must know that there are countries which do not read dates in this format. For example DAMASCUS(top of my mind) read Dates in 'mm_dd_yyyy'.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, February 13, 2014 6:31 AM
  • OK, I apologize for being rude. Now will Celko apologize to me for being rude?

    John Schroeder


    • Edited by jschroeder Monday, February 17, 2014 1:26 AM
    Monday, February 17, 2014 1:21 AM