Why do I get a conversion failed error when I use my attribute on my WHERE clause but it works when I don’t?

Why is my query returning a result when I run the following query:

SELECT MAX(CAST(IssueDate as Date))
FROM (SELECT IssueDate
      FROM [Transient].[Commissions_TIB]
      WHERE ISDATE(issuedate) = 1
      GROUP BY IssueDate) t

But when I use the ‘IssueDate’ attribute in my WHERE clause it fails to convert to a date from a string?

SELECT MAX(CAST(IssueDate as Date))
FROM (SELECT IssueDate
      FROM [Transient].[Commissions_TIB]
      WHERE ISDATE(issuedate) = 1
      GROUP BY IssueDate) t
WHERE CAST(IssueDate as Date) <= CAST(GETDATE() as date)

Answer

Most likely, there are IssueDates in your table that are not valid. For some reason, SQL Server seems to decide to apply the outer predicate before the inner predicate. I suspect that it relates to a query planner optimization technique called predicate pushdown – and, here, this might be considered a bug…

In a nutshell: don’t use isdate(). It is not safe anyway, as it relies on some complicated (and undisclosed) heuristic. Instead, use try_convert() or try_cast(), which check and convert at once.

Your whole query can be simplified as:

SELECT MAX(TRY_CAST(IssueDate as Date))
FROM [Transient].[Commissions_TIB]
WHERE TRY_CAST(IssueDate as Date) <= CAST(GETDATE() as date)