This question already has an answer here:
I have a
datetime I want to convert to a formatted date and time. The format is 109(9). I have always used:
CONVERT(char(26), c1.DateTime, 109)
Which works successfully, but at least on SQL Server 2014 the Cardinality Estimator is throwing a warning:
Type conversion in expression (CONVERT(char(26),c1.DateTime,109)) may affect "CardinalityEstimate" in query plan choice.
I would like to correct this if possible at all, but maybe this is the improved cardinality estimator I am not sure.
The full query in this contrived example is from a temp table populated from an real table which is what I am trying to correct.
CREATE TABLE #Vic ( DateStamp datetime not null, TIN int not null ); GO INSERT INTO #Vic VALUES ( N'1985-09-06T00:00:00', 123456789 ), ( N'2003-01-14T20:55:00.983', 123456789 ), ( N'1997-08-07T00:00:00', 123456789 ), ( N'2016-11-15T09:31:28.15', 123456789 ), ( N'2003-01-14T20:45:29.247', 123456789 ); GO SELECT TOP (10) CONVERT(char(26), v.DateStamp, 109) , CONVERT(varchar(26), v.DateStamp, 9) , CONVERT(nvarchar(26), v.DateStamp, 9), CONVERT(nvarchar(30), v.DateStamp, 9), CONVERT(varchar(9), v.TIN) FROM #Vic AS v;
This gives a warning on every column listed.
This may be related to this Connect Item, but I am not sure the context is the same based on the query this Connect item provides.