参数化的ODBC查询可与VarChar一起使用，但不能与DateTime一起使用?

本文介绍了参数化的ODBC查询可与VarChar一起使用，但不能与DateTime一起使用?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

作为此问题的后续操作 ，当我手动将2016-05-01之类的日期定义为字符串/varchars时，一切正常.但是，当我转换为日期时间时，现在又得到了空结果.这是目前的代码:

As followup to this question, everything was working when I was manually defining the dates like 2016-05-01 as strings/varchars. However, when I went to convert to datetime I'm now getting empty results again. This is the code as it stands:

log("Connecting to SQL Server..."); string connectionString = "DSN=HSBUSTEST32;"; // Provide the query string with a parameter placeholder. string queryString = "SELECT COUNT(*) FROM Table WHERE myDateTime >= ? AND myDateTime < ?"; // Specify the parameter value. DateTime startDate = DateTime.Now; DateTime endDate = startDate.AddHours(-1); using (OdbcConnection connection = new OdbcConnection(connectionString)) { // Create the Command and Parameter objects. OdbcCommand command = new OdbcCommand(queryString, connection); command.Parameters.Add("@startDate", OdbcType.DateTime).Value = startDate; command.Parameters.Add("@endDate", OdbcType.DateTime).Value = endDate; try { connection.Open(); OdbcDataReader reader = command.ExecuteReader(); while (reader.Read()) { log(reader[0].ToString()); } reader.Close(); } catch (Exception ex) { log(ex.Message); } }

同样，如果我要替换以下内容:

Again, if I were to replace the following:

DateTime startDate = DateTime.Now; DateTime endDate = startDate.AddHours(-1);

与

string startDate = "2016-08-23"; string endDate = "2016-08-24";

然后将OdbcType更改为VarChar一切正常.

And then change the OdbcType to VarChar everything works fine.

推荐答案

我相信您的错误与日期范围有关.

I believe your error is with the date range.

// Specify the parameter value. DateTime startDate = DateTime.Now; DateTime endDate = startDate.AddHours(-1);

endDate将比开始日期少1个小时.您查询中的比较运算符大于第一个参数且小于第二个参数.

endDate will be 1 hour less than start date. The comparison operator in your query is greater than first parameter and less than second parameter.

例如:

string queryString = "SELECT COUNT(*) FROM Table WHERE myDateTime >= '8/26/2016 14:30:00' AND myDateTime < '8/26/2016 13:30:00'";

不存在大于同一日期的2:30 pm且小于1:30 pm的日期. :)

No date exists that's greater than 2:30pm and less than 1:30pm of the same date. :)

也许你是说

DateTime startDate = DateTime.Now; DateTime endDate = startDate.AddHours(1);