转化率从字符串转换日期时间时失败(Conversion failed when converting

我想从Windows应用程序传递参数给存储过程。有两个参数“@dt”＆“@ DT2”，这得到datetimepickers值。每当我执行存储过程它与错误说出现“从字符串转换日期时间时，转换失败。”

执行查询后，我想在一个DataGridView来显示它。

我无法找出问题..这里是我的程序和代码

存储过程

ALTER PROC [DBO]。[deepak_proc]

（@dt日期时间，@ DT2日期时间）

如

声明@cols AS NVARCHAR（MAX）

声明@query AS NVARCHAR（MAX）

选择@cols =

  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,'')

设置@query

 = '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;'

执行（@query）

而下面的代码是按键点击

的SqlConnection CON =新的SqlConnection（）; con.ConnectionString = @ “数据源= \ SQLEXPRESS;初始目录= TEST_DB;用户ID =用户;密码=密码”;
  con.Open(); SqlCommand cmd = new SqlCommand("deepak_proc", con); cmd.CommandType = CommandType.StoredProcedure; 
cmd.Parameters.Add（ “@ DT”，SqlDbType.DateTime）。价值= dateTimePicker1.Value.ToString（ “MM / DD / YYYY”）;
cmd.Parameters.Add（ “@ DT2”，SqlDbType.DateTime）。价值= dateTimePicker2.Value.ToString（ “MM / DD / YYYY”）;
数据表DT =新数据表（）;
cmd.ExecuteNonQuery（）;
sda.Fill（DT）;
dataGridView1.DataSource = DT;
con.Close（）;

Answer 1:

使用sp_executesql的系统存储过程，并把你的变量parametrs

SET @query
= '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], 
                       ktimeDown1 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;'

EXEC sp_executesql @query, N'@dt datetime, @dt2 datetime', @dt, @dt2

Answer 2:

尝试的日期时间通入YYYY-MM-DD格式是标准的。

Answer 3:

听起来好像你得到从dateTimePicker的客户端的日期的字符串表示。当您从dateTimePicker的获取的价值，将其转换为一个真正的CLR Date对象的客户端 。如果有映射到SQL类型CLR类型不通过字符串后端。建立命令对象客户端时，选择适当的参数的类型。

在一般情况下，保持时间存在的日期，并将其转换为字符串，只有当你需要显示他们对人的眼睛。

编辑：例如：

        SqlClient.SqlParameter  p0;

        p0.ParameterName = "@startdate";
        p0.SqlDbType = SqlDbType.DateTime;
        p0.Value =  < a datetime object, not a string>  
        p0.Direction = ParameterDirection.Input;

然后将参数添加到Parameters集合。

Answer 4:

DateTime myDateTime = Convert.ToDateTime(dateTimePicker1.Value);

cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter
("@myDateTime", MySql.Data.MySqlClient.MySqlDbType.DateTime));
 cmd.Parameters["@myDateTime"].Value = myDateTime;