title: Conversion failed when converting date and/or time from character string dynamic sql
body: <p>I've got a bit of dynamic SQL I'm running that is working fine when I manually write in the variables, but as soon as I then change them from the manually written in to actual variables, I get the error above.</p>
  
  <p>The code is:</p>
  
  <pre><code>set @query = 'SELECT eng, ' + @colsNull + ' 
          from 
          (
              select eng, [count], cast(weekof as date) weekof
              from dbo.RPT_ENG_WEEK (''1 jan 2013'', ''9 apr 2013'', ''1 jan 2013'', ''9 apr 2013'')
          ) x
          pivot 
          (
              sum([count])
              for weekof in (' + @cols + ')
          ) p '
  </code></pre>
  
  <p>Which works fine, but as soon as I change it to </p>
  
  <pre><code>set @query = 'SELECT eng, ' + @colsNull + ' 
          from 
          (
              select eng, [count], cast(weekof as date) weekof
              from dbo.RPT_ENG_WEEK ('+@from+', '+@to+', '+@start+', '+@end+')
          ) x
          pivot 
          (
              sum([count])
              for weekof in (' + @cols + ')
          ) p '
  </code></pre>
  
  <p>I get the error.  I've also tried</p>
  
  <pre><code>set @query = 'SELECT eng, ' + @colsNull + ' 
          from 
          (
              select eng, [count], cast(weekof as date) weekof
              from dbo.RPT_ENG_WEEK ((select CONVERT(DATE'+@from+',105)), (select CONVERT(DATE'+@to+',105)), (select CONVERT(DATE'+@start+',105)), (select CONVERT(DATE'+@end+',105)))
          ) x
          pivot 
          (
              sum([count])
              for weekof in (' + @cols + ')
          ) p '
  </code></pre>
  
  <p>But to no avail!</p>
  
  <p>Have also tried </p>
  
  <pre><code>from dbo.RPT_ENG_WEEK ((select CONVERT(DATE'''+@from+''',105)), (select CONVERT(DATE'''+@to+''',105)), (select CONVERT(DATE'''+@start+''',105)), (select CONVERT(DATE'''+@end+''',105)))
  </code></pre>
  
  <p>and</p>
  
  <pre><code>from dbo.RPT_ENG_WEEK ('''+@from+''', '''+@to+''', '''+@start+''', '''+@end+''')
  </code></pre>