4 Replies Latest reply on Apr 14, 2018 8:14 PM by Ken Flerlage

    "[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string."

    Vardaan Thapa

      Hi Guys,

       

      Above is the error I am getting while using parameters in the Custom sql query as data source.

       

      Here is the original query written in the Custom sql :

      select * from tsr_test.vw_Startdata
      where marketdate=<Parameters.pStart Date>
      and <Parameters.pStart Date> >= [EarliestDate]
      and <Parameters.pEnd Date> <= [LatestDate]
      and company=<Parameters.pCompany>
      and <Parameters.pStart Date> < <Parameters.pEnd Date>
      and datediff(mm, <Parameters.pStart Date>,<Parameters.pEnd Date>)>=12

      UNION
      SELECT * FROM tsr_test.vw_enddata
      where marketdate = <Parameters.pEnd Date>
      and company= <Parameters.pCompany>
      and <Parameters.pStart Date> < <Parameters.pEnd Date>
      and <Parameters.pStart Date> >= [EarliestDate]
      and <Parameters.pEnd Date> <= [LatestDate]
      and datediff(mm, <Parameters.pStart Date>,<Parameters.pEnd Date>)>=12

       

      And Here is how it is rendered by tableau to be run on the SQL server :

      SELECT (CASE WHEN (([Custom SQL Query].[sales] = 0) OR ([Custom SQL Query].[netinc] = 0)) THEN 'NM' ELSE CAST(ROUND([Custom SQL Query].[Pe_Margin],1,0) as nvarchar) END) AS [Calculation_933934011326328833],

        [Custom SQL Query].[marketdate] AS [marketdate],

        SUM([Custom SQL Query].[Pe_Margin]) AS [sum:Calculation_734931200166227969:ok]

      FROM (

        select * from tsr.vw_Startdata

        where marketdate={d '1998-01-31'}

        and company='3M Company'

        and {d '1998-01-31'} < {d '2009-0006-30'}

        and datediff(mm, {d '1998-01-31'},{d '2009-0006-30'})>=12

        UNION

        SELECT * FROM tsr.vw_enddata

        where marketdate = {d '2009-0006-30'}

        and company= '3M Company'

        and {d '1998-01-31'} < {d '2009-0006-30'}

        and {d '1998-01-31'} >= EarliestDate

        and datediff(mm, {d '1998-01-31'},{d '2009-0006-30'})>=12

      ) [Custom SQL Query]

      GROUP BY (CASE WHEN (([Custom SQL Query].[sales] = 0) OR ([Custom SQL Query].[netinc] = 0)) THEN 'NM' ELSE CAST(ROUND([Custom SQL Query].[Pe_Margin],1,0) as nvarchar) END),

        [Custom SQL Query].[marketdate]

       

      Here , we can see that tableau converts the parameter <Parameters.pEnd Date> as {d '2009-0006-30'}. Now this format is not valid in SQL hence the error. Why is Tableau converting the date parameter into an invalid date format??  Please Help friends. This has been bugging me for quite some time now.