0

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.

  • The above question goes into some good depth on this. If you're not having any performance issues, this probably does not matter at all. But if you really want to know why despite that, I'd suggest adding the execution plan so people can more easily dissect what is going on here. – LowlyDBA Oct 9 at 20:37
  • Okay.@LowlyDBA.The only question i have here is why implicit conversion when i am doing the conversion explicitly.Thanks for directing to the other post. – user9516827 Oct 9 at 21:11
  • Try to rewrite the function such a way SELECT "DateValue" = CAST( NULLIF( ISDATE(@stringvalue), 0 ) AS DATETIME ) – Denis Rubashkin Oct 10 at 6:31