Transact SQL date query returning no results

Question

From my code, I call an SP using:

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.

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.

TIA.

Answer 1

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

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:

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

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

Answer 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:

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