title: Conversion failed when converting datetime from character string
body: <p>I am trying to pass parameters from windows application to a stored procedure.
  there are two parameters '@dt' &amp; '@dt2' which get value from datetimepickers.
  whenever i execute the stored procedure it comes up with the error saying "Conversion failed when converting datetime from character string."</p>
  
  <p>After executing the query i want to display it in a datagridview.</p>
  
  <p>i am unable to figure out the problem..
  here is my procedure and code</p>
  
  <p>Stored Procedure</p>
  
  <blockquote>
    <p>ALTER proc [dbo].[deepak_proc]</p>
    
    <p>( @dt datetime, @dt2 datetime )</p>
    
    <p>AS</p>
    
    <p>declare @cols AS NVARCHAR(MAX)</p>
    
    <p>declare @query  AS NVARCHAR(MAX)</p>
    
    <p>select @cols =</p>
  
  <pre><code>              STUFF((SELECT ',' + QUOTENAME(date +'_'+Logname) 
                  from
                  (
                    select K_date,
                      convert(char(10),K_date, 101) date, 
                      LogName
                    from kq_report_analyze
                    cross apply
                    (
                      select 'In' LogName
                      union all
                      select 'Out' 
                    ) l
                  ) s   
                  group by convert(char(10), K_date, 112), date, Logname
                  order by convert(char(10), K_date, 112)
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
      ,1,1,'')
  </code></pre>
    
    <p>set @query</p>
  
  <pre><code>= 'SELECT empid, name, status, '+@cols+' FROM (SELECT empid, name, status, 
     doortime, date + "_" + col AS col_names FROM  (SELECT k_userid
     AS [empid], K_Name AS name, k_description1 as [status], K_WorktimeUp1
     AS [IN], K_WorktimeDown1 AS OUT, CONVERT(char(10), K_Date, 101) AS
    date FROM  dbo.kq_report_analyze WHERE (K_Date BETWEEN '+@dt+' AND
    '+@dt2+') GROUP BY K_UserID, K_Name, k_description1, K_Date,
    K_WorktimeUp1, K_WorktimeDown1) src  UNPIVOT (doortime FOR col IN
    ([IN], [OUT])) unpiv) 
    p PIVOT (max(doortime) FOR col_names IN ('+@cols+')) piv;'
  </code></pre>
    
    <p>execute(@query)</p>
  </blockquote>
  
  <p>And the following code is on button click</p>
  
  <blockquote>
    <p>SqlConnection con = new SqlConnection();
    con.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=test_db;User ID=user;Password=password";</p>
  
  <pre><code>     con.Open();
  
  
         SqlCommand cmd = new SqlCommand("deepak_proc", con);
  
  
     cmd.CommandType = CommandType.StoredProcedure;
  </code></pre>
    
    <p>cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = dateTimePicker1.Value.ToString("MM/dd/yyyy");</p>
    
    <p>cmd.Parameters.Add("@dt2", SqlDbType.DateTime).Value = dateTimePicker2.Value.ToString("MM/dd/yyyy"); </p>
    
    <p>DataTable dt = new DataTable();</p>
    
    <p>cmd.ExecuteNonQuery();</p>
    
    <p>sda.Fill(dt);</p>
    
    <p>dataGridView1.DataSource = dt;</p>
    
    <p>con.Close();</p>
  </blockquote>