title: Passing datetime paramters in Dynamic SQL, Conversion failed when converting date and/or time from character string
body: <p>I have a property in a table called cdate which is stored as such: 2014-10-07 05:26:17.897.</p>
  
  <p>my dynamic  query is something like this, CDATE is a datetime in the table and startdate and enddate  are always passed and they are both datetime as well. My where clause is in a dynamic SQL query and it returns some records but for instance  if CDate = 2014-10-07 05:26:17.897 and I am passing start date as 09/30/2010 08:15 pm and end date 10/07/2014 08:15 pm, i do not get my intended record back</p>
  
  <pre><code>SELECT  
  CONVERT(VARCHAR(10),ServiceEntry.CDate,120) as CDate
  FROM TABLE
  WHERE
  (Table.CDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')  
  </code></pre>
  
  <p><strong>my original query</strong></p>
  
  <pre><code>DECLARE @PivotTableSQL NVARCHAR(MAX)
  SET @PivotTableSQL = N'
     SELECT *
     FROM (
       SELECT
         TSBNumber [TSBNumber],
         SystemFullName,
         CONVERT(VARCHAR(10),ServiceEntry.ClosedDate,120) as ClosedDate
       FROM ServiceEntry 
       inner JOIN System 
         ON ServiceEntry.SystemID = System.ID
       where
        (ServiceEntry.TSBNumber IS NOT NULL)
         AND 
         (ServiceEntry.ClosedDate IS NOT NULL)
         AND
         ( 
         (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
           (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
          )  
          AND (
           (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''') 
          OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
          OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID &lt;=  ''' + '2' + ''')
          )
          AND
     (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')    
  </code></pre>