1

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.

marked as duplicate by Mikael Eriksson, Paul White Jan 21 '17 at 22:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.