MS Access OLE DB: DateTime parameters with milliseconds cause "Data type mismatch" error

[DEllingsworth]

MS Access OLE DB: DateTime parameters with milliseconds cause "Data type mismatch" error

Description

When using PetaPoco with Microsoft Access databases via OLE DB provider, inserting or updating records with DateTime values that include milliseconds causes an OleDbException: Data type mismatch in criteria expression.

Environment

• Database: Microsoft Access (.mdb or .accdb)
• Provider: System.Data.OleDb (Microsoft.ACE.OLEDB.12.0 or Microsoft.Jet.OLEDB.4.0)
• PetaPoco Version: Current development branch

Root Cause

MS Access OLE DB provider cannot handle DateTime parameters with sub-second precision (milliseconds). When PetaPoco passes DateTime objects with milliseconds to the OLE DB provider, Access rejects them.

Reproduction

var person = new Person
{
    Id = Guid.NewGuid(),
    Name = "Test Person",
    Age = 40,
    Dob = new DateTime(2000, 6, 15, 14, 30, 45, 123, DateTimeKind.Utc) // Has 123 milliseconds
};

// Throws: OleDbException: Data type mismatch in criteria expression
db.Insert(person);

Expected Behavior

The insert should succeed, with milliseconds truncated to ensure Access compatibility.

Actual Behavior

Throws System.Data.OleDb.OleDbException: Data type mismatch in criteria expression

Workaround

Manually truncate milliseconds before insert:

var dt = DateTime.UtcNow;
var truncated = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, dt.Kind);
person.Dob = truncated;

Status

A PR with a fix and regression test is forthcoming.