如何将毫秒的日期时间插入Access数据库? [英] How to insert datetime with milliseconds into Access database?

问题描述

我正在尝试使用C#将日期+时间插入Access数据库.时间字符串中包含毫秒，例如:

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"`

这是我的代码:

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();
}

我在 ExcuteNonQuery()处收到错误；如果我删除日期时间字符串，则代码有效.

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

那我如何将毫秒的日期时间插入Access数据库?

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

推荐答案

您正在混淆DateTime和String.始终使用DateTime作为日期/时间值，或尽早转换为DateTime.因此:

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

请注意，尽管Access将正确存储该值，但它或VBA不会显示毫秒，而是将4/5舍入到秒.当然，如果您仅从C#应用程序中读取这些值，则这无关紧要.

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

这篇关于如何将毫秒的日期时间插入Access数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！