Passing a DateTime value as a parameter to an OleDbCommand

Question

I have a problem passing a DateTime value to a query as a DbParameter. It seems the time part of the DateTime value gets stripped away.

Here is a sample code in C#:

DbProviderFactory _factory = OleDbFactory.Instance;

DbCommand cmd = _factory.CreateCommand();
cmd.CommandText = "INSERT INTO SomeTable (SomeDateField) VALUES (?)";

DbParameter p = _factory.CreateParameter();
p.ParameterName = ""; // Not necessary
p.Value = DateTime.Now; // assume Time != 00:00:00
p.DbType = DbType.Date; // DateTime and DateTime2 don't work

cmd.Parameters.Add(p);

My problem is that the Date parameter does not seem to reach Access with it's time part and SomeDateField always has 00:00:00 as time.

I don't want to do something like:

cmd.CommandText = "INSERT INTO SomeTable (SomeDateField) VALUES (#" + aDateTimeString + "#)";

What is the datatype of the column in the table of the database? Also What do you mean by `DbType.DateTime` doesn't work? — Jonathan Henson, Jul 01 '11 at 19:13
When I use DbType.DateTime and DbType.DateTime2 I get a 'Data type mismatch in criteria expression' exception when I execute the command. — Gerardo Contijoch, Jul 07 '11 at 21:38
I use Access as backend so the column data type is Date, but it's the equivalent to DateTime in .NET, it can contain the time part too. — Gerardo Contijoch, Jul 07 '11 at 21:43
The following answer suggests that the milliseconds in the DateTime value may be a problem: https://stackoverflow.com/a/29207251/13087 So you could try rounding/truncating to the nearest second if you want to avoid using provider-specific data types (assuming this precision is sufficient for your use case). — Joe, Nov 09 '19 at 13:29

score 0 · Answer 1 · answered May 18 '13 at 08:23

OleDbType does not have a DateTime enumeraton http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

If you use DBType.DateTime, "The type of a parameter is specific to the .NET Framework data provider. Specifying the type converts the value of the Parameter to the data provider Type before passing the value to the data source. If the type is not specified, ADO.NET infers the data provider Type of the Parameter from the Value property of the Parameter object. " http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

score 0 · Answer 2 · answered Jul 01 '11 at 19:12

0

Make sure your data type for SomeDateField is DateTime and not Date. Also, try to make

p.DbType = DbType.DateTime;

answered Jul 01 '11 at 19:12

Adriano Carneiro

57,693
12
90
123

As I said in another comment, I get an exception when I use DateTime and DateTime2. The only one that works is Date. – Gerardo Contijoch Jul 07 '11 at 21:39

score 0 · Answer 3 · answered Jul 01 '11 at 19:16

When you'd use OleDbType.DateTime instead of DbType.Date, I'm sure it will work.

But, as I understand from your post, you do not want to be so specific, and use the more general DbType enum (and classes) ?

However, I think you should use the more specific OleDb classes in your data-access layer. Using the less specified 'DbType' classes, is kind of useless, because, when you're targetting another database-type, chances are quite big that your SQL-syntax will have to change as well, since each DBMS uses its own dialect (sometimes only minor changes, but still ... ).

Well, I was working with some code that already had the less specific interfaces exposed, that's why I was using them. I can change that, but I think it shouldn't be necesary. I'll try using OleDb clases and see if that helps. — Gerardo Contijoch, Jul 07 '11 at 21:41

Passing a DateTime value as a parameter to an OleDbCommand

3 Answers3

Linked