事务SQL日期查询，不返回任何结果

From my code, I call an SP using:

根据我的代码，我使用以下方法调用SP:

using (var cmd = new SqlCommand("sp_getnotes"))
{
  cmd.Parameters.Add("@ndate", SqlDbType.SmallDateTime).Value
                  = Convert.ToDateTime(txtChosenDate.Text);
  cmd.CommandType = commandType;
  cmd.Connection  = conn;

  var dSet = new DataSet();
  using (var adapter = new SqlDataAdapter { SelectCommand = cmd })
  {
    adapter.Fill(dSet, "ntable");
  }
}

The Stored Procedure itself runs a simple query:

存储过程本身运行一个简单的查询:

SELECT * FROM tblNotes WHERE DateAdded = @ndate

The problem is no records are returned! DateAdded is a smalldatetime column.

问题是没有记录被返回!dateadd是一个smalldatetime列。

When I change the query to the following, it works:

当我将查询更改为以下时，它可以工作:

SELECT * FROM tblNotes WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, DateAdded))) = @ndate

Why is this happening? This change affects the entire application and I'd like to find the root cause before getting into changing every single query... The only changes we made are to use parameterized queries and upgrade from SQL Server 2005 to 2008.

为什么会这样?这个更改会影响整个应用程序，我希望在修改每个查询之前找到根本原因……我们所做的唯一更改是使用参数化查询并从SQL Server 2005升级到2008。

TIA.

TIA。

3 个解决方案

#1

smalldatetime has a time portion which needs to match as well.

smalldatetime还有一个时间部分需要匹配。

Use this:

用这个:

SELECT  *
FROM    tblNotes
WHERE   dateAdded >= CAST(@ndate AS DATE)
        AND dateAdded < DATEADD(day, 1, CAST(@ndate AS DATE))

SQL Server 2008 and above also let you use this:

SQL Server 2008和以上也让你使用这个:

SELECT  *
FROM    tblNotes
WHERE   CAST(dateAdded AS DATE) = CAST(@ndate AS DATE)

efficiently, with the transformation to a range performed by the optimizer.

有效地，将转换为优化器执行的范围。

#2

SQL Server 2008 now has a DATE data type, which doesn't keep the time porttion like SMALLDATETIME does. If you can't change the data type of the column, then you'll have to truncate when doing the compare, or simply cast to DATE:

SQL Server 2008现在有一个日期数据类型，它不像SMALLDATETIME那样保留时间传送。如果您不能更改列的数据类型，那么在进行比较时，您将不得不截断，或者简单地按日期转换:

SELECT  * 
FROM    tblNotes 
WHERE   cast(dateAdded as date) = @ndate

#3

I don't know SQL Server but from Oracle experience I'd suspect you're comparing a date time with a date, eg 01/01/2012 01:01:01 against 01/01/2012.

我不知道SQL Server，但是根据Oracle的经验，我怀疑您是在比较日期时间和日期，例如01/01/2012 01:01和01/2012。

#1