title: How can I use SQL BETWEEN to compare two dates in a string to execute to sp_executesql?
body: <p>I have a line in my SQL Stored Procedure that looks like this (works as intended):</p>
  
  <pre><code>HAVING  oh.startdate BETWEEN @startDate AND @endDate
  </code></pre>
  
  <p>However, further down I have the line:</p>
  
  <pre><code>AND (oh.user IN (@userIDs))
  </code></pre>
  
  <p>Where @userIDs is a comma delimited string of ID's and oh.user is an INTEGER, so I must actually put the entire SQL query into a dynamic string (@sql) with all the parameters concatenated into it and then use </p>
  
  <pre><code>sp_executesql @sql 
  </code></pre>
  
  <p>Everything is working fine except the BETWEEN dates, I've tried a few ways and keep getting errors or no results returned when there should be:</p>
  
  <pre><code>    HAVING  oh.startdate BETWEEN CONVERT(DATETIME, '+LEFT(CONVERT(VARCHAR, @startDate, 120), 10)+', 120) AND CONVERT(DATETIME, '+LEFT(CONVERT(VARCHAR, @endDate, 120), 10)+', 120)
  </code></pre>
  
  <p>returns nothing. </p>
  
  <pre><code>    HAVING  oh.startdate '+LEFT(CONVERT(VARCHAR, @startDate, 120), 10)+' AND '+LEFT(CONVERT(VARCHAR, @endDate, 120), 10)+'
  </code></pre>
  
  <p>also returns nothing.</p>
  
  <pre><code>    HAVING  oh.startdate BETWEEN ' + @startDate +' AND '+ @endDate +'
  </code></pre>
  
  <p>returns error converting DATETIME to string. </p>
  
  <p>Any help is appreciated. </p>
  
  <p>Thanks,
  Thomas</p>