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.