参数化的ODBC查询可与VarChar一起使用，但不能与DateTime一起使用? [英] Parameterized ODBC Query works with VarChar but not 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);

这篇关于参数化的ODBC查询可与VarChar一起使用，但不能与DateTime一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！