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