Explicit conversion to avoid the implicit conversion warning

by user9516827   Last Updated October 09, 2019 21:06 PM - source

I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below.

WITH X 
          AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus,
                     (SELECT DiaryDateTime FROM Diary r 
                          WHERE (r.BatchNumber = 'N0000065') AND r.ItemCode = '33568') AS DiaryDateTime         
                     FROM CurrentDiaryStatus cis 
                     INNER JOIN Diary r WITH (NOLOCK) ON (cis.DiaryId = r.Id)
                     WHERE (r.BatchNumber = 'N0000065') AND r.ItemCode = '33568')
    SELECT X.CurrentDiaryStatus,
           X.DiaryDateTime,
      CA4.DiaryMaxDate
    FROM X
    OUTER APPLY (VALUES(DATEADD(DAY, 30, X.DiaryDateTime))) CA1(DiaryMaxDate)
    OUTER APPLY (VALUES(DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.DiaryMaxDate AS TIME), 1, 0)
                                                    + DATEDIFF(DAY, CA1.DiaryMaxDate, GETDATE()) ) / 30.0), CA1.DiaryMaxDate))) CA2(DiaryMaxDate)
    OUTER APPLY (SELECT datevalue FROM dbo.CastOfDate((SELECT TOP 1 pj.Date
                                       FROM ProductionJournalPage_ProductionJournal pj 
                                       INNER JOIN ProductionJournalPages p ON pj.PageId = p.Id 
                                       INNER JOIN Event e ON p.EventId = e.Id 
                                       INNER JOIN Diary r WITH (NOLOCK) ON (e.DiaryId = r.Id)
                                       WHERE (r.BatchNumber = 'N0000065' AND r.ItemCode = '33568') AND 
                                       pj.ReviewType = 'Sup Monthly Review' ORDER BY pj.Date DESC))) CA3(ProductionJournalDate)
    OUTER APPLY (VALUES ( 
    CASE
    WHEN ( DATEADD(DAY, 30, X.DiaryDateTime) < GETDATE() AND ( CA3.ProductionJournalDate IS NULL
    OR DATEADD(DAY, 30, CA3.ProductionJournalDate) < GETDATE() ) AND DATEADD(DAY, 14, CA2.DiaryMaxDate) > DATEADD(DAY, 30, GETDATE()) )
    THEN DATEADD(DAY, -30, CA2.DiaryMaxDate)
    WHEN( ( CA3.ProductionJournalDate IS NOT NULL ) AND ( DATEADD(DAY, 30, CA3.ProductionJournalDate) >= CA2.DiaryMaxDate ) )
    THEN DATEADD(DAY, 30, CA2.DiaryMaxDate)
                          ELSE CA2.DiaryMaxDate
                        END )) CA4(DiaryMaxDate);

I am returning DiaryMaxDate based on some calculation and it all works fine except the below issue. I see that the execution plan for this query has Explicit conversion warnings.The conversion is happening for the date column pj.Date column. The table ProductionJournalPage_ProductionJournal has the column as varchar and so i am converting the pj.Date column to Datetime using another TVF functiuon CastOfDate. I have the column pj.Date converted to datetime and called as CA3(ProductionJournalDate) and i am using it in the outer apply for some calculations.

Even though i am converting it explicitly,why am i getting conversion warnings again ,saying that SQL has to convert it.??

This is the conversion warning from SQL execution plan.

Type conversion in expression (CONVERT(datetime,CONVERT_IMPLICIT(varchar(50),[Expr1031],0),0)) may affect "CardinalityEstimate" in query plan choice

Expr1031 is the fully qualified column name for the Date.

CastOfDate is another TVF function which cast string date values to datetime. Here is its definition.

CREATE FUNCTION [dbo].[CastofDate]
(
    @stringvalue varchar(50)
)
RETURNS TABLE
AS 
RETURN 
(
SELECT "DateValue" =
    CASE
    WHEN ISDATE(@stringvalue) != 0 
    THEN  CAST(@stringvalue AS DATETIME)
    ELSE NULL
    END
);
GO

I know that i can ignore this warnings if the performance is not bad,but i have couple of functions similar to this which calculates few dates and i am referencing those dates in another query which is a complex one and i there i see lots(~20) of these implicit conversion warnings and makes the query slow.

To repeat the question again

Even though i am converting it explicitly,why am i getting conversion warnings again ,saying that SQL has to convert it again.?

I don't think it is duplicate because ,I am explicitly converting the field to avoid the conversion sql server has to do or to avoid the implicit conversion also i am not inserting any values instead i am reading the data.



Related Questions






Warning in query plan "Cardinality Estimate"

Updated February 08, 2019 21:06 PM