Why is IDbDataParameter updating an Access DateTime field with the year 2001 instead of 0001?

567 Views Asked by At

Why is IDbDataParameter updating a MS Access DateTime field with the year 2001 instead of 0001? I am using IDbDataParameter. It works well with SQL server but not MS Access. I thought I had tested this earlier...

IDbDataParameter dp;
var dbType = GetDatabaseType(Cmd.Connection);
for (int n = 0; n < Parameters.Length; n++)
{
var param = Parameters[n];
dp = Cmd.CreateParameter();
dp.ParameterName = paramNames[n];
TypeCode myTypeCode = Type.GetTypeCode(param.GetType());
if (myTypeCode == TypeCode.DateTime)     // this workaround is needed for MS Access and SQL Server
{
  if (dbType == DatabaseType.OleDb)
    dp.DbType = DbType.DateTime;         // set dates as DbType.DateTime for MS Access and Paradox
  else if (dbType == DatabaseType.MSSql)
    dp.DbType = DbType.DateTime2;        // set dates as DbType.DateTime2 for SQL Server
  dp.Value = param.ToString();
}
else
  dp.Value = param;
Cmd.Parameters.Add(dp);

Update1 Incorrect times sometimes also when setting DateTime in Access (OleDb connection > IDbConnection > IDbCommand)

10 seconds saves as: 2001-01-01 00:28:32 // dp.Value = param.ToString();

If you format the string it also saves as 2001 and now uses 12:00 noon: 2001-01-01 12:00:46 // dp.Value = ConvertToDateTime(param).ToString("yyyy-MM-dd hh:mm:ss");

Update2

/* Tests when executing insert and update queries against MS Access (OleDb Jet) and DateTime field using value of '0001-01-01 00:00:00'.
  * DateTime d = Convert.ToDateTime(param);
  * dp.Value = new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second); // Insert/Update = 2001-01-01
  * dp.Value = Convert.ToDateTime(param).ToString("yyyy-MM-dd hh:mm:ss");        // Insert/Update = 2001-01-01 00:00:00
  * dp.Value = param.ToString();                 // Insert/Update = 2001-01-01
  * dp.Value = Convert.ToDateTime(param);        // Insert/Update = 2001-01-01
  * dp.Value = param;                            // Insert/Update = 2001-01-01
  * dp.Value = new DateTime(1, 1, 1, 0, 0, 0);   // Insert/Update = 2001-01-01
  */

/* Tests when executing query against MS Access (OleDb Jet) and DateTime field using value of '2010-10-10 10:10:10'.
  * DateTime d = Convert.ToDateTime(param);
  * dp.Value = new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second); // Insert/Update = 2010-10-10 10:10:10
  * dp.Value = Convert.ToDateTime(param).ToString("yyyy-MM-dd hh:mm:ss");        // Insert/Update = 2010-10-10 10:10:10
  * dp.Value = param.ToString();                 // Insert/Update = 2010-10-10 10:10:10
  * dp.Value = Convert.ToDateTime(param);        // Insert/Update = 2010-10-10 10:10:10
  * dp.Value = param;                            // Insert/Update = 2010-10-10 10:10:10
  * dp.Value = new DateTime(2010, 10, 10, 10, 10, 10);   // Insert/Update = 2010-10-10 10:10:10
  */
0

There are 0 best solutions below