Trying to insert DateTime.Now into Date/Time field gives "Data type mismatch" error

Question

If I try to write a datetime to a record in an MS-Access database the easy way, like this

cmd.CommandText = "INSERT INTO [table] ([date]) VALUES (?)";
cmd.Parameters.AddWithValue("?", DateTime.Now);

I get an exception saying "Data type mismatch in criteria expression."

Can anybody tell me why? What goes wrong here?

After a little experimentation, I found that I can make it work if I write

OleDbParameter parm = new OleDbParameter("?", OleDbType.Date);
parm.Value = DateTime.Now;
cmd.Parameters.Add(parm);

but doing it like this seems less neat, less straightforward. Why is this necessary? Am I overlooking something simple?

Answer 1

The problem of the mismatch in criteria expression is due to the OleDbType assigned to the parameter used to represent the DateTime.Now value when you call AddWithValue.

The OleDbType choosen by AddWithValue is DBTimeStamp, but Access wants a OleDbType.Date.

http://support.microsoft.com/kb/320435

Searching on the NET I have found another intersting tip. The core problem lies in the OleDbParameter that cannot handle the milliseconds part of the DateTime.Now. Probably forcing the OleDbType to be Date the milliseconds part is omitted. I have also found that the insert works also with the DBTimeStamp type if we remove the milliseconds from the date.

cmd.Parameters.AddWithValue("?", GetDateWithoutMilliseconds(DateTime.Now));

private DateTime GetDateWithoutMilliseconds(DateTime d)
{
    return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second);
}

oh, well, waiting for someone that explain this better.

Answer 2

The simplest statement asks the db engine to use its Now() function to get the current Date/Time value. Or you could use its Date() function if you aren't interested in the time of day; Date() will actually give you midnight as time of day.

INSERT INTO [table] ([date]) VALUES (Now());

IOW, you needn't bother massaging a Date/Time value in .Net in order to insert it into your Access db.

If you want an INSERT statement which includes a literal date value, use the # date delimiters. So to insert today's date:

INSERT INTO [table] ([date]) VALUES (#2013-04-25#);