Access ODBC驱动程序中无效的日期时间格式异常 [英] Invalid datetime format exception from Access ODBC Driver

问题描述

我有一些从ODBC驱动程序（基于DSN字符串（带有用户名和密码）选择驱动程序）读取的.NET代码，该驱动程序从表中读取多个字段，其中一个字段是DateTime字段。该代码在SQL Server数据库/ ODBC驱动程序中的使用时间为100％，在大多数情况下，与MS Access数据库一起使用。但是，有时我会在特定行上得到第2列（DateTimeColumn）上无效的日期时间格式异常，甚至不直接访问该列（例如，即使我只是调用

I have some .NET code that reads from an ODBC driver (the driver is selected based on a DSN string (with username and password)) that reads several fields from a table, one of which fields is a DateTime field. The code works 100% of the time with SQL Server databases/ODBC drivers and most of the time with MS Access databases. However, sometimes I will get an "Invalid datetime format on column number 2 (DateTimeColumn)" exception on a particular row without even accessing that column directly (for example, even if I just call

reader.IsDBNull(someOtherColumn)

我仍然

这似乎大部分（仅？）是在Access数据库中已经填充了Excel中计算某些DateTimes的数据（例如加1）时发生的。 / 24到日期时间以获取下一个小时）。

This seems to mostly (only?) happen when the Access database has been populated with data from Excel where some of the DateTimes were calculated (e.g. adding 1/24 to a datetime to get the next hour).

如果我运行以下查询，则异常消失：

The exception goes away if I run the following query:

UPDATE MyTable Set DateTimeColumn = CDate(CStr(DateTimeColumn))

因此，从Excel的日期时间计算到Access驱动程序的日期时间计算，似乎存在某种舍入误差。

Thus, it would appear that there is some kind of rounding error involved from Excel's calculation of datetimes to the Access driver's calculation of datetimes.

由于这些数据中的一些由创建自己的数据库的用户提供，我将无法使用我的代码在其数据库上运行UPDATE查询。一种可能的仅访问解决方法是在我的SQL语句中调用CDate（CStr（DateTimeColumn）），但不适用于SQL Server或其他数据库。

Since some of this data is being provided by users who created their own database, I won't be able to run UPDATE queries on their database with my code. One potential Access-only workaround would be to call CDate(CStr(DateTimeColumn)) in my SQL statement, but that won't work for SQL Server or other databases.

我仅使用可同时使用.mdb文件和.accdb文件的32位MS Access驱动程序（我的计算机上没有64位驱动程序进行测试）对此进行了测试，并且不管是否数据位于.mdb文件或.accdb文件中。

I have tested this only with the 32-bit MS Access driver (I don't have the 64-bit drivers on my machine to test them) that works with both .mdb files and .accdb files, and the problem occurs regardless of whether the data is in a .mdb file or a .accdb file.

编辑：

为将来参考，在Access数据库中导致异常的 Date / Time 值是 0x40E4277FFFFFFFF8 ，十进制是

For future reference, the Date/Time value in the Access database that was causing the Exception was 0x40E4277FFFFFFFF8 which in decimal is

41275.9999999999417923390865326

41275.9999999999417923390865326

还有可能的兴趣是，当尝试通过<$ c读取行时，该值导致错误$ c> OdbcConnection ，使用 OleDbConnection 读取同一行并没有抛出异常。

Also of possible interest is that while this value caused an error when trying to read the row via an OdbcConnection, reading the same row using an OleDbConnection did not throw an exception.

推荐答案

以下工作台考虑到来自更新后问题的测试数据，ound似乎可以正常工作。它检查打开的连接的 .Driver 属性，以查看它是否正在从Access数据库中读取。如果是这样，它将检索日期/时间的值作为 Double ，然后将其转换回 System.DateTime 。否则，它只会从SQL Server正常检索 datetime 。

The following workaround seems to work given the test data from the updated question.. It checks the .Driver property of the open connection to see if it is reading from an Access database. If so, it retrieves the Date/Time value as Double and then converts it back to System.DateTime. Otherwise, it just retrieves the datetime from SQL Server normally.

（请注意，这种特殊方法–使用 rdr [0]）* 86400 – 在早期版本的Access中的 Date / Time 值中将无法正常工作小于 1899-12-30 00:00:00 ，即 Double 的值为负。）

(Please note that this particular approach – using rdr[0]) * 86400 – will not work correctly for Date/Time values in Access that are earlier than 1899-12-30 00:00:00, i.e., when the Double value is negative.)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;

namespace odbcTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OdbcConnection con = new OdbcConnection())
            {
                //con.ConnectionString =
                //        @"Driver={SQL Server};" +
                //        @"Server=(local)\SQLEXPRESS;" +
                //        @"Database=myDb;" +
                //        @"Trusted_connection=yes;";
                con.ConnectionString =
                        @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
                        @"Dbq=C:\__tmp\dateTest\TestSqlRead.accdb;";
                con.Open();
                using (OdbcCommand cmd = new OdbcCommand())
                {
                    DateTime dtm;
                    var accessTime0 = new DateTime(1899, 12, 30);
                    bool fromAccess = (con.Driver == "ACEODBC.DLL");
                    cmd.Connection=con;
                    if (fromAccess)
                        //cmd.CommandText = "SELECT DateTimeCol FROM MyTable";  // this fails
                        cmd.CommandText = "SELECT {fn CDbl(DateTimeCol)} FROM MyTable";
                    else
                        cmd.CommandText = "SELECT sqlDate FROM Table1 WHERE ID = 1";
                    OdbcDataReader rdr = cmd.ExecuteReader();
                    rdr.Read();
                    if (fromAccess)
                        dtm = accessTime0.AddSeconds(Convert.ToDouble(rdr[0]) * 86400);
                    else
                        dtm = Convert.ToDateTime(rdr[0]);
                    Console.WriteLine(dtm.ToString());
                    rdr.Close();
                }
                con.Close();
            }
            Console.WriteLine();
            Console.WriteLine("Done.");
            Console.ReadKey();
        }
    }
}

这篇关于Access ODBC驱动程序中无效的日期时间格式异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！