Access ODBC驱动程序抛出无效的日期时间格式异常。

Question

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

reader.IsDBNull(someOtherColumn)

我仍然遇到异常。

这似乎主要发生在访问数据库已经填充了从Excel中计算的日期时间数据时（例如添加1/24到日期时间以获取下一个小时）。

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

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

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

由于一些数据是由创建自己数据库的用户提供的，我将无法使用我的代码在他们的数据库上运行UPDATE查询。一个潜在的仅适用于Access的解决方法是在我的SQL语句中调用CDate(CStr(DateTimeColumn))，但这对于SQL Server或其他数据库不起作用。

我只使用32位MS Access驱动程序进行了测试（我没有64位驱动程序来测试它们），该驱动程序可与.mdb文件和.accdb文件一起使用，无论数据是在.mdb文件还是.accdb文件中，问题都会发生。

编辑：

以后参考，引起异常的Access数据库中的值为<0x40E4277FFFFFFFF8>，在十进制中为

41275.9999999999417923390865326

还可能感兴趣的是，虽然使用OdbcConnection读取该行时此值会导致错误，但使用OleDbConnection读取相同行却没有抛出异常。

- Jeremy

---

您能够找出引起异常的带有日期值的记录，将其复制到单独的 Access 数据库文件中，并使该文件可供下载（例如通过 wikisend.com 或类似的方式）吗？我花了几分钟用十六进制编辑器对一个 .accdb 文件进行了一些尝试，试图创建“奇怪”的日期值，但 ACE ODBC 没有抱怨其中任何一个。 - Gord Thompson

Gord，感谢您的时间。请告诉我您是否能够访问编辑后问题中的链接。 - Jeremy

Answer 1

以下解决方法似乎适用于更新问题中给出的测试数据。它检查打开连接的.Driver属性，以查看它是否正在从Access数据库中读取。如果是，则将Date/Time值作为Double检索，然后将其转换回System.DateTime。否则，它只会正常地从SQL Server检索datetime。（请注意，这种特定方法-使用rdr[0]) * 86400-对于早于1899-12-30 00:00:00的Access中的Date/Time值（即Double值为负数）不起作用。）

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

- Gord Thompson

---

使用con.Driver（或con.DataSource ==“ACCESS”）是我的最初想法，但我担心ACEODBC.DLL字符串可能会随着访问驱动程序版本的更改而改变（64位驱动程序是否具有相同的.dll？） 您选择CDbl（）而不是CDate（CStr（））是出于性能考虑吗？我担心如果从Excel复制到Access时存在四舍五入问题，则AddSeconds（）可能会在我认为自己得到下一天时给我午夜之前。 - Jeremy

@user3000697 是的，无论是32位还是64位平台，DLL的名称都是ACEODBC.DLL。名称可能会更改，但我不认为会更改。我选择了CDbl()，因为Access将日期存储为Double数字。CDate(CStr())存在风险，因为在默认日期格式为dd-mm-yyyy的机器上，它可能会搞乱模糊的日期，例如4月2日可能会神秘地变成2月4日。至于舍入误差，一旦您获得了Double值（而不是代码崩溃），您可以根据需要进行调整；我只是使用.AddSeconds()，因为这是最直接的方法。 - Gord Thompson

CDate()和CStr()在MS Access中真的不是互逆的吗？这很可怕 - 我本来以为转换一个方向会安全地转换回另一个方向。感谢您的帮助。 - Jeremy

@Jeremy 从一开始，Jet/ACE就始终将模糊的日期文字解释为mm-dd-yyyy，而不管机器本身的区域设置如何。例如，请参见我的答案这里。 - Gord Thompson

如果我想要使用相同的OdbcDataReader代码，只使用不同的SELECT语句（因此只有一个“if（fromAccess）”），您是否认为以下查询存在任何问题：SELECT CDate(Round(CDbl(DateTimeCol),8))（我不关心时间分辨率小于百分之一秒的情况）这似乎解决了问题。 - Jeremy

@Jeremy 访问 日期/时间 值只支持到整秒的分辨率，所以这不应该是一个问题。 - Gord Thompson

Answer 2

没有。

Access使用自己的日期格式。

'#2011-12-16#'

SQL-Server 使用 ISO 标准来表示日期值：

20111216

而且

2012-12-16THH:mm:ss.fff

关于日期时间

您可以将包含日期值的表作为字符串提取到System.Data.DataTable中，更正这些值，然后使用update将它们写回。更好的做法是永远不要使用文本字段来存储日期/时间，这样您就不会遇到这个问题，因为它不允许您插入无效的日期时间值（也许Access除外...）。

此外，如果您使用参数化查询进行插入，您就不会遇到这个问题。

如果您从客户那里得到一个Excel文件，您需要验证Excel表格中的日期值是否有效。
如果无效，则需要有人更正Excel文件中的数据。

• 日期必须始终匹配相同的格式（MM-DD-YYYY），不能有时是(dd.MM.yyyy)
• 日期必须大于最小值，例如SQL-Server中的01.01.1753（特别重要，因为日期格式为MM-DD-YY）
• 日期不能超过SQL-Server中的31.12.9999 23:59:59.995

如果您使用access + SQL Server，则最小日期值必须是两个最小日期值的最大值，最大日期值必须是两个最大日期值的最小值。

- Stefan Steiger

---

我是否误读了你的回答，还是你的大部分评论似乎假定我将DateTime存储为字符串，而不是DateTime？你关于使用参数化查询的建议很好，但我认为.mdb文件不支持参数化查询——在Access中保存查询的视图是否同样好？ - Jeremy

@user3000697 是的，您可以对ACE/Jet（Access）数据库运行参数化查询。在Access中保存的查询可以作为参数查询，但仅在使用DAO时才真正有用。对于.NET下的ODBC，只需使用OdbcCommand对象并给它一些.Parameter即可。这里有很多示例展示如何使用OleDbCommand对象进行操作，而OdbcCommand对象的工作方式也是相同的。 - Gord Thompson

抱歉 - 我混淆了参数化查询（我在Access中使用过）和存储过程，以为Quandary建议使用存储过程来处理DateTime字符串的不一致格式。 - Jeremy