I have a table like below:

Name: VW_Questionnaire

The below code works:

SELECT Question, Response
From VW_Questionnaire
WHERE QuestionId = 2
      AND CAST(Response AS Date) < SELECT (CURRENT_TIMESTAMP)

The below code throws an error:

SELECT Question, Response
From VW_Questionnaire
WHERE Tag = 'SP1'
      AND CAST(Response AS Date) < SELECT (CURRENT_TIMESTAMP)

Conversion failed when converting date and/or time from character string.

It seems like in the first example, it first filters to WHERE QuestionId = 2 and then it successfully can CAST Response AS Date.

However, in the second example, it seems like it's attempting to CAST Response AS Date before it filters to WHERE Tag = 'SP1'.

I even tried to do this with a CTE where within the CTE I do the filter to WHERE Tag = 'SP1' and then I SELECT from this filtered table and CAST Response AS date. I'm still met with the conversion error.

Any idea why this is happening?

CodePudding user response：

SQL is a declarative language. This means that the server is free to reorder your predicates as it sees fit, in order to hit indexes and improve performance.

Therefore, you cannot rely on the predicates being executed in left-to-right order. Instead, you can just use TRY_CAST, which will null out any invalid values

SELECT Question, Response
FROM VW_Questionnaire
WHERE QuestionId = 2
  AND TRY_CAST(Response AS Date) < CURRENT_TIMESTAMP;

Note that the point about reordering even applies in derived tables and views. For example, you may still get an error in this version:

SELECT Question, Response
FROM (
    SELECT *
    FROM VW_Questionnaire
    WHERE QuestionId = 2
) v
WHERE CAST(Response AS Date) < CURRENT_TIMESTAMP;

The only construct guaranteed to evaluate from left-to-right is CASE (and even that guarantee only applies to scalar values, not aggregates).

I strongly recommend you ensure your data is in the correct format in the first place, so you don't need to cast it.