title: Dynamic SQL in SSRS giving in error in converting the parameter to datetime
body: <p>I am trying to run this sp in ssrs but it is giving me the following error -</p>
  
  <p>Failure to convert the parameter value from String to Datetime.</p>
  
  <p>I don't understand what I am doing wrong here. It is a dynamic SQL. I know the error is in the date conversion where the date parameter is converted. The four parameters are - 
  @dtAdjustedStart,@dtAdjustedEnd,@dtAdjustedStartLY,@dtAdjustedEndLY. Can anyone please help me on this -----</p>
  
  <pre><code>ALTER PROC SSRS_Specials_Performance_Report(@DB varchar(30),@cOperators  varchar(50),@dtAdjustedStart DATETIME,@dtAdjustedEnd DATETIME, @dtAdjustedStartLY   DATETIME,@dtAdjustedEndLY DATETIME,@cSalespoints 
  varchar(30),@vcRankingColumn varchar(30), @vcRankingDirection varchar(30))
  
    AS 
  
  DECLARE @SQL VARCHAR(MAX)
  
  
  
  
  IF OBJECT_ID('tempdb..#transact_ty') IS NOT NULL DROP TABLE #transact_ty;
  CREATE TABLE #transact_ty
  (
  special    CHAR(10),
  quantity   INT,
  admissions INT,
  pr_ctr_1   INT,
  pcsplit_1  MONEY,
  pr_ctr_2   INT,
  pcsplit_2  MONEY,
  pr_ctr_3   INT,
  pcsplit_3  MONEY,
  pr_ctr_4   INT,
  pcsplit_4  MONEY,
  pr_ctr_5   INT,
  pcsplit_5  MONEY,
  pr_ctr_6   INT,
  pcsplit_6  MONEY
  );
  
  
  IF OBJECT_ID('tempdb..#transact_ly') IS NOT NULL DROP TABLE #transact_ly;
  CREATE TABLE #transact_ly
  (
  special    CHAR(10),
  quantity   INT,
  admissions INT,
  pr_ctr_1   INT,
  pcsplit_1  MONEY,
  pr_ctr_2   INT,
  pcsplit_2  MONEY,
  pr_ctr_3   INT,
  pcsplit_3  MONEY,
  pr_ctr_4   INT,
  pcsplit_4  MONEY,
  pr_ctr_5   INT,
  pcsplit_5  MONEY,
  pr_ctr_6   INT,
  pcsplit_6  MONEY
  )
  
  IF OBJECT_ID('tempdb..#ReportOutput') IS NOT NULL DROP TABLE #ReportOutput;
  CREATE TABLE #ReportOutput
  (
   Special            CHAR(10),
   SpecialDescription VARCHAR(25),
   SpecialAdmitsTY    INT,
   SpecialRevenueTY   MONEY,
   SpecialPctOfTotal  NUMERIC(12,4),
   SpecialRevenueLY   MONEY,
   ProfitCenter       VARCHAR(25),
   RevenueCenter      VARCHAR(25),
   AdmissionsTY       INT,
   RevenueTY          MONEY,
   TotalRevenueTY     MONEY,
   AdmissionsLY       INT,
   RevenueLY          MONEY,
   TotalRevenueLY     MONEY,
   AdmissionsVariance INT,
   RevenueVariance    MONEY
   );
  
   SET @SQL = 'INSERT INTO #transact_ty
   SELECT t.special,
     t.quantity,
     t.admissions,
     t.pr_ctr_1,
     t.pcsplit_1,
     t.pr_ctr_2,
     t.pcsplit_2,
     t.pr_ctr_3,
     t.pcsplit_3,
     t.pr_ctr_4,
     t.pcsplit_4,
     t.pr_ctr_5,
     t.pcsplit_5,
     t.pr_ctr_6,
     t.pcsplit_6
     FROM ' + @DB + '.dbo.transact t
      WHERE t.date_time BETWEEN CAST(''' + CONVERT(varchar,@dtAdjustedStart,120) + '''    AS DATE) AND CAST('''+ CONVERT(varchar,@dtAdjustedEnd,120) + ''' AS DATE) 
     AND t.operator IN ('''+ @cOperators + ''')
     AND t.salespoint IN (''' + @cSalespoints + ''')'
  
     EXEC(@SQL)
  
    SET @SQL = 'INSERT INTO #transact_ly
    SELECT t.special,
     t.quantity,
     t.admissions,
     t.pr_ctr_1,
     t.pcsplit_1,
     t.pr_ctr_2,
     t.pcsplit_2,
     t.pr_ctr_3,
     t.pcsplit_3,
     t.pr_ctr_4,
     t.pcsplit_4,
     t.pr_ctr_5,
     t.pcsplit_5,
     t.pr_ctr_6,
     t.pcsplit_6
     FROM ' + @DB + '.dbo.transact t
     WHERE t.date_time BETWEEN CAST('''+ CONVERT(varchar,@dtAdjustedStartLY,120) + ''' AS DATE) AND CAST(''' + CONVERT(varchar,@dtAdjustedEndLY,120)+ ''' AS DATE)
     AND t.operator IN (''' + @cOperators + ''')
     AND t.salespoint IN (''' + @cSalespoints + ''')'
  
  
    EXEC(@SQL)
  </code></pre>