2

I'm getting an error when I try to run a simple aggregating query.

SELECT MAX(CAST(someDate as datetime)) AS MAX_DT FROM #SomeTable WHERE ISDATE(someDate) = 1

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

Non-date entries should be removed by WHERE clause, but that doesn't seem to be happening. I can work around with an explicit CASE statement inside the MAX(), but I don't want to hack up the query if I can avoid it. If I use a lower COMPATIBILITY_LEVEL, it works fine. If I have fewer than 2^17 rows, it works fine.

-- SQLServer 15.0.4043.16
USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 150;
GO

-- delete temp table if exists
DROP TABLE IF EXISTS #SomeTable;
GO

-- create temp table
CREATE TABLE #SomeTable (
    someDate varchar(20) DEFAULT GETDATE()
);

-- load data, need at least 2^17 rows with at least 1 bad date value
INSERT #SomeTable DEFAULT VALUES;

DECLARE @i int = 0;

WHILE @i < 17
BEGIN

    INSERT INTO #SomeTable (someDate) SELECT someDate FROM #SomeTable
    SET @i = @i + 1;

END
GO

-- create invalid date row
WITH cteUpdate AS (SELECT TOP 1 * FROM #SomeTable)
UPDATE cteUpdate SET someDate='NOT_A_DATE'

-- error query
SELECT MAX(CAST(someDate as datetime)) AS MAX_DT
FROM #SomeTable 
WHERE ISDATE(someDate) = 1

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

-- delete temp table if exists
DROP TABLE IF EXISTS #SomeTable;
GO
GMB
  • 216,147
  • 25
  • 84
  • 135
FmlyMaan
  • 31
  • 2

2 Answers2

3

I would recommend try_cast() rather than isdate():

SELECT MAX(TRY_CAST(someDate as datetime)) AS MAX_DT
FROM #SomeTable

This is a much more reliable approach: instead of relying on some heuristic to guess whether the value is convertible to a datetime (as isdate() does), try_cast actually attempts to convert, and returns null if that fails - which aggregate function max() happily ignores.

try_cast() (and sister functions try_convert()) is a very handy functions, that many other databases are missing.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

I actually just encountered the same issue (except I did cast to float). It seems that the SQL Server 2019 Optimizer sometimes (yes, it's not reliable) decides to execute the calculations in the SELECT part before it applies the WHERE.

If you set compatibility level to a lower version this also results in a different optimizer being used (it always uses the optimizer of the compatibility level). Older query optimizers seem to always execute the WHERE part first.

Seems lowering the compatibility level is already the best solution unless you want to replace all CAST with TRY_CAST (which would also mean you won't spot actual errors as easily, such as a faulty WHERE that causes your calculation to then return NULL instead of the correct value).

Benjamin
  • 1