SQL Server Date Conversion – Datetime to Formatted Date Implicit Conversion

cardinality-estimatessql server

I am looking at implicit conversions in my stored procedures and the biggest one I have in my prod database is a warning by the Cardinality Estimator in SQL 2014 for converting datetime to formatted date.

I have read the following questions:
Warning in query plan "Cardinality Estimate" and
DateTime to Format: Implicit Conversion but these fail to mention that FORMAT() removes the warning they are only describing that the problem won't be fixed, which is not a solution I looking for, FORMAT() is a viable solution.

Previously we used the formatting option on CONVERT() to format the date, but ANYTIME I use CONVERT() I get an implicit conversion.

CREATE TABLE #temptable ( [Birthdate] datetime )
INSERT INTO #temptable
VALUES
( N'1891-12-30T00:00:00' ), 
( N'1918-10-31T00:00:00' ), 
( N'1917-12-21T00:00:00' ), 
( N'1906-10-29T00:00:00' ), 
( N'1916-12-12T00:00:00' ), 
( N'1916-11-08T00:00:00' ), 
( N'1918-10-01T00:00:00' ), 
( N'1913-03-18T00:00:00' ), 
( N'1920-08-03T00:00:00' ), 
( N'1919-10-22T00:00:00' )


SELECT CONVERT(VARCHAR(26),t.Birthdate,109) AS varConvert,
        CONVERT(NVARCHAR(26), t.Birthdate, 109) AS nvarConvert,
        TRY_CONVERT(VARCHAR(26),t.Birthdate, 109) AS tryCon,
        FORMAT(t.Birthdate, 'MMM dd yyyy') AS form
FROM #temptable AS t

DROP TABLE #temptable

The only not implicit conversion to a formatted date is the FORMAT() function:

enter image description here

Is there a way to not use the FORMAT() function and continue using the Convert function to return formatted dates? Ie do i need to extend or reduce the length of my formatted dates during conversion. I want to avoid implicit conversions, but re-writting all CONVERT() to FORMAT() will take more testing than changing the current CONVERT functionality.

Best Answer

The query you posted does indeed produce PlanAffectingConvert warnings in SQL Server 2016 SP1 (didn't try on earlier versions).

That's a bug - those convert statements shouldn't be throwing that warning since it doesn't affect cardinality in any way when they're just in the SELECT portion.

File a bug report for it at http://connect.microsoft.com and include your repro code, and edit your question to include a link to the Connect item so folks can upvote it.

Related Question