考虑到更新后的问题中的测试数据,以下变通办法似乎可以正常工作。它检查.Driver
打开的连接的属性,以查看它是否正在从Access数据库中读取。如果是这样,它将获取Date/Time
as的值Double
,然后将其转换回System.DateTime
。否则,它将仅从datetime
sql Server正常检索。
(请注意,此特定方法使用`rdr[0]) * 86400`''对于Access中早于的值(即,当值为负时) **将无法** 正常工作。)`Date/Time
1899-12-30 00:00:00`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();
}
}
}