title: CAST varchar to datetime in dynamic SQL
body: <p>I keep getting the following error:</p>
  
  <blockquote>
    <p>Msg 241, Level 16, State 1, Line 9 Conversion failed when converting
    datetime from character string.</p>
  </blockquote>
  
  <p>Here is the code I am trying to execute:</p>
  
  <pre><code>  DECLARE @v_sql  varchar(max),
    @v_database varchar(25),
    @vStartTime     DATETIME,
    @vEndTime       DATETIME
  
  SELECT @v_database = N'[DATABASE_NAME]', @vStartTime = '2012-09-27', @vEndTime = '2012-11-27'
  
  
  SELECT @v_sql = N'SELECT
    (SELECT ID FROM DATASTORE.DBO.PLANT WHERE DESCRIPTION = ''Henderson''),
    SEQ, 
    AID, 
    NAME, 
    GRP, 
    AREA, 
    PRIO,
    CASE ITIME WHEN ''-'' THEN NULL ELSE CAST(SUBSTRING(ITIME,1,8) + '' '' + SUBSTRING(ITIME,9,2) + '':'' + SUBSTRING(ITIME,11,2) + '':'' + SUBSTRING(ITIME,13,2) AS DATETIME) END ITIME,
    CASE ATIME WHEN ''-'' THEN NULL ELSE CAST(SUBSTRING(ATIME,1,8) + '' '' + SUBSTRING(ATIME,9,2) + '':'' + SUBSTRING(ATIME,11,2) + '':'' + SUBSTRING(ATIME,13,2) AS DATETIME) END ATIME,
    CASE NTIME WHEN ''-'' THEN NULL ELSE CAST(SUBSTRING(NTIME,1,8) + '' '' + SUBSTRING(NTIME,9,2) + '':'' + SUBSTRING(NTIME,11,2) + '':'' + SUBSTRING(NTIME,13,2) AS DATETIME) END NTIME,
    DUR, 
    MSG,
    VAR1,
    VAR2,
    VAR3,
    VAR4, 
    OPR, 
    USER_COMMENT
  FROM ' + @v_database + '.PROD.ALARM
  WHERE CAST(substring(ITIME, 1, 4) + ''-'' + substring(ITIME, 5, 2) + ''-'' + substring(ITIME, 7, 2) + '' '' + substring(ITIME,9,2) + '':'' + substring(ITIME,11,2) + '':'' + substring(ITIME,13,2) + substring(ITIME,15,3) AS DATETIME) BETWEEN  ' + @vStartTime +' AND ' + @vEndTime + ' ORDER BY ITIME'
  EXEC(@v_sql)
  </code></pre>
  
  <p>Any help would be much appreciated, I am looking into this for a co-worker and it's got us both stumped.</p>
  
  <p><strong>Edit</strong> with a bit more digging, we were able to resolve it ourselves, passing parameters to <code>sp_executesql</code>:</p>
  
  <pre><code>declare
    @vSql     NVARCHAR(MAX),
    @vParam    NVARCHAR(MAX),
    @vDatabase       VARCHAR(15)
  
  SET @vParam = '@vStartTime DATETIME, @vEndTime DATETIME'
  
   SELECT @vSql = '
      SELECT ''
        '+ @vDatabase + ''',
        ITEM_CODE,
        SOURCE,
        DEST,
        TRAN_DT,
        MILL_NAME,
        NULL
      FROM ' + @vDatabase + '.PROD.GRD_LOG
      WHERE TRAN_DT BETWEEN @vStartTime AND @vEndTime'
  
  EXEC sp_executesql @vSql, @vParam, @vStartTime, @vEndTime
  </code></pre>
  
  <p>By making the variables <code>NVARCHAR(MAX)</code>, and then using <code>sp_executesql</code> instead of just executing the <code>@vSql</code> variable, we were able to resolve our issue.</p>
  
  <p>Thanks to anyone who might have been looking into this.</p>