Im getting an error on this statement:

FROM

[My Table]

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 :

Yes.

Make the ISDATE condition a subquery or CTE.

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.

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

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 NModerator Thursday, December 19, 2013 5:49 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

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.

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. 

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 NModerator Thursday, December 19, 2013 7:55 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 NModerator Thursday, December 19, 2013 7:55 PM
• Unproposed as answer by Pan ZhangMicrosoft contingent staff, Administrator Tuesday, December 24, 2013 8:25 AM
• Proposed as answer by Pan ZhangMicrosoft contingent staff, Administrator Tuesday, December 24, 2013 8:26 AM

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.

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.

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" e

Are you suggesting I rebuild the entire application I've inherited? is classic! Lets 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 :) 

Have you considered writing SQL and not dialect?

Have you?

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 12 hours 55 minutes ago

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

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! 

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:

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. 

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.

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

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.

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.

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.

So why isn't BOL using the "safe" format?

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

BOL screenshot:

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.

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