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:

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: