问题描述
作为此问题的后续操作 ,当我手动将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);
这篇关于参数化的ODBC查询可与VarChar一起使用,但不能与DateTime一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!