How to insert datetime with milliseconds into Access database?

Question

I am trying to insert a date + time into an Access database using C#. The time string has milliseconds in it, e.g.:

"2015-03-23 11:22:33.123"`

Here is the code I have:

string strName = "somestring";
string strDate = "2015-03-23 11:22:33.123"

sql = @"insert into table_name  ([name], [date]) values (@Name, @Date)";

using (OleDbCommand command = new OleDbCommand(sql))
{
    command.Connection = openCon;
    command.Parameters.AddWithValue("@Name", strName);
    command.Parameters.AddWithValue("@Date", strDate ); // <-- error here          
    openCon.Open();
    recordsAffected = command.ExecuteNonQuery();
    openCon.Close();
}

I get an error at ExcuteNonQuery(); if I remove the date time string, then the code works.

So how do I insert datetime with milliseconds into an Access database?

Answer 1

You are mixing up DateTime and String. Always use DateTime for date/time values or convert to DateTime as early as possible. Thus:

string strDate = "2015-03-23 11:22:33.123";
DateTime datDate = DateTime.Parse(strDate);
// ...
//    command.Parameters.AddWithValue("@Date", datDate ); // <-- error here 

command.Parameters.Add("@Date",OleDbType.Date);
command.Parameters[0].Value = datDate;                    // <-- no error

Note that although Access will store the value correctly, it or VBA will not display the milliseconds but do a 4/5 rounding to the second. This is, of course, of no importance if you only read these values from your C# application.

The value will be inserted correctly as can be seen here when inserted in my Millisecond application:

DateTimeMs          SortValue       DateFull                CDouble             RoundSecSQL         MsecSQL MsecVBA SecondDec SecondStd
23-03-2015 11:22:33 60438568953123  23-03-2015 11:22:33.123 42086,4739944792    42086,4739930556    123     123     33,123    33
23-03-2015 11:45:31 60438570330707  23-03-2015 11:45:30.707 42086,4899387384    42086,4899305556    707     707     30,707    31

Answer 2

The Access "Date/Time" field type has a nominal resolution of one second. That is, when Date/Time values are displayed in Access, formatted using pre-defined or custom formats, or manipulated with functions like DateAdd or DatePart, the smallest unit of time we can specify is a second.

Using OLEDB with .NET

When using .AddWithValue against an Access database via System.Data.OleDb, a DateTime parameter for a Date/Time field must not have a Millisecond value or the insert/update will fail. That is,

DateTime dt = Convert.ToDateTime("2015-03-23 11:22:33.123");
cmd.Parameters.AddWithValue("?", dt);
cmd.ExecuteNonQuery();

will fail with

Data type mismatch in criteria expression.

However, as Gustav points out in his answer we can use .Add with OleDbType.Date to add a DateTime parameter that does include milliseconds,

DateTime dt = Convert.ToDateTime("2015-03-23 11:22:33.123");
cmd.Parameters.Add("?", OleDbType.Date).Value = dt;
cmd.ExecuteNonQuery();

and the value will be stored, intact, in the Access Date/Time field. We can verify this by retrieving the stored value and checking it

cmd.CommandText = "SELECT [date] FROM table_name WHERE ID = 13";
DateTime rtn = Convert.ToDateTime(cmd.ExecuteScalar());
Console.WriteLine(rtn.Millisecond);

... which prints "123", showing that the millisecond component is part of the returned value.

The trick is that Access itself does not give us the tools to effectively work with the millisecond portions of those Date/Time values. To do so from within Access/VBA requires some extra coding as explained in an article on another site here.

(Of course, if you're only interested in pulling the values back into a .NET application then you can just use the code snippet above.)

Using ODBC with .NET

Using the same approach with System.Data.Odbc yields slightly different results. The Access ODBC driver will accept a DateTime parameter with a Millisecond value for both .Add and .AddWithValue but it will NOT store the milliseconds.

Also, if we try to retrieve a Date/Time value with milliseconds that was stored using OLEDB, the Access ODBC driver removes the milliseconds. That is, if we try to use the following ODBC code to retrieve the "millisecond value" we stored via the OLEDB example above ...

// cmd is now an OdbcCommand object
cmd.CommandText = "SELECT [date] FROM table_name WHERE ID = 13";
DateTime rtn = Convert.ToDateTime(cmd.ExecuteScalar());
Console.WriteLine(rtn.Millisecond);

... it prints "0", even though we can verify that the millisecond portion of the Double value has been stored in the database by doing

cmd.CommandText = "SELECT CDbl([date]) AS foo FROM table_name WHERE ID = 13";
Double rtn = Convert.ToDouble(cmd.ExecuteScalar());
Console.WriteLine(rtn);

and inspecting the decimal portion of the Double value.

Alternatives

If you want to stay within the nominal 1-second resolution of the Date/Time field type you can ...

Round the value to the nearest second:

DateTime dt = Convert.ToDateTime("2015-03-23 11:22:33.123");
if (dt.Millisecond >= 500)
{
    dt = dt.AddSeconds(1);
}
dt = dt.AddMilliseconds(-dt.Millisecond);
cmd.Parameters.AddWithValue("?", dt);
cmd.ExecuteNonQuery();

Or, if you want to keep the milliseconds then you will either have to store the truncated (not rounded) Date/Time and the milliseconds in separate fields, or perhaps just store the original string value in a Text field.

Answer 3

You have to try like this:

command.Parameters.AddWithValue("@Date", GetDateWithMilliseconds(strDate));

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

The reason why you are getting the error is because when you use OleDbType by AddWithValue then it is DBTimeStamp, but Access wants a OleDbType.Date. Check the MSDN for details

Or you can try like this:

command.Parameters.Add("@Date", OleDbType.Date).Value = strDate;