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?

Electoral

date might be a keyword here. Try INSERT INTO table ([date]) VALUES (?) – Indamine

@Indamine You're right about it being a keyword, of course. I updated the code in the question. But that's not the cause of the problem. – Electoral

Now that we know the issue is milliseconds, by using just the type of Date you lopped off the time – Sedum

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.

Bricole

+1, Which means cmd.Parameters.AddWithValue("?", DateTime.Today); would work. – Indamine

@Indamine yes, but the time part is gone – Bricole

Sacrifices have to be made with an Access database. :-) – Indamine

Love and hate at the nth degree. – Bricole

Thanks; this explains why the call works with most datetimes, except Now. And it probably works fine once in a thousand times with Now too! By the way, I'm not using Access by choice, you know. But hey, it's a paying customer. – Electoral

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#);

Gownsman

I think he's using C# not VBA – Oldenburg

@Oldenburg The programming language doesn't matter because Now() is a built-in function in the ACE/Jet database engines. – Merwyn

@Oldenburg Access SQL supports its own built-in Now() and Date() functions. This is not a c# vs VBA type issue. That SQL statement will work when executed from a valid connection to the db regardless of programming language. – Gownsman

@Gownsman Thanks. This does actually help me with simplifying my code, since that's what I need to store in this case. But my question still stands, why does the DB engine complain about a DateTime not being the right type to put into a Date/Time field. – Electoral

Format the date as yyyy-mm-dd and surround it with # delimiters. – Gownsman

Ehm, I want to use parameters if at all possible. – Electoral

In that case, in your first example, format the parameter value as yyyy-mm-dd and surround it with # delimiters. But your question is misleading --- you should clarify that you want something broader than insert Now(). It seems now you're asking to insert any Date/time value. – Gownsman

@Gownsman I'm sorry if you think I steered you in the wrong direction with the example in my question; I didn't mean to. I did say "Inserting dates". And although your answer helped with my issue, it doesn't answer my actual question. Sorry. – Electoral

@Gownsman reach for the sky (Rory Gallagher reference): I used your approach, and it works just fine. – Frizzy