Random programming things I'd want to remember

Friday, June 27, 2014

Pull data by date from MS Access using C# no records returned

I am playing with MS Access database and a WPF app. I generated a bunch of data and the C# DateTime values of, say, 6/27/2010, inserted in the database turned out as 6/27/2010 12:00:00. Then the funny thing happened, I was trying to pull the following query:

using(OleDbCommand cmd = "SELECT * FROM Table1 WHERE MyDate = ?")
{
  cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate;
  try
  {
    conn.Open();
    ...
  }
}


And it just did not pull any rows. I tried running it just in Access, and it worked fine in the query designer, I was getting rows back. This puzzled me until I read this KB. I then ran the following query in MS Access:

SELECT DISTINCT MyDate, CDbl(MyDate) FROM Table1 ORDER BY 1


And I got some interesting results:

Expr1000         Expr1001
6/27/2010         40356
6/27/2010 12:00:00 40356.5


So if a datae is stored as just 6/27/2010, it's a midnight of the beginning of the day. But if it has 12:00:00 time suffix, it is a noon of that day, so it's not the same as midnight and my query above would not pull it. But the funny thing is is that when I created data, I only specified the date part and nothing on time, so something is happening between C# and MS Access that C# sends 12:00:00 as midnight and MS Access understands it as noon. I even tried the following to no avail:

cmd.Parameters.Add("@val1", OleDbType.DBDate).Value = myDate;
cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate.Date;
cmd.Parameters.Add("@val1", OleDbType.Date).Value = new DateTime(myDate.Year, myDate.Month, myDate.Day);


Same thing, no results from the command, while the query in MS Access worked just fine.

A workaround:

using(OleDbCommand cmd = "SELECT * FROM Table1 WHERE MyDate BETWEEN ? AND ?")
{
  cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate.Date;
  cmd.Parameters.Add("@val2", OleDbType.Date).Value = myDate.Date.AddDays(1);
  try
  {
    conn.Open();
    ...
  }
}


And that works.

No comments: