Excel Jet OLE DB: Inserting a DateTime value

Question

OLEDB can be used to read and write Excel sheets. Consider the following code example:

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
    cmd.ExecuteNonQuery();
    cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
    cmd.Parameters.AddWithValue("@mydate", DateTime.Now.Date);
    cmd.ExecuteNonQuery();
}

This works perfectly fine. Inserting numbers, text, etc. also works well. However, inserting a value with a time component fails:

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
    cmd.ExecuteNonQuery();
    cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
    cmd.Parameters.AddWithValue("@mydate", DateTime.Now); // <-- note the difference here
    cmd.ExecuteNonQuery();
}

Executing this INSERT fails with an OleDbException: Data type mismatch in criteria expression.

Is this a known bug? If yes, what can be done to workaround it? I've found one workaround that works:

cmd = new OleDbCommand(String.Format(@"INSERT INTO Sheet1 VALUES (#{0:dd\/MM\/yyyy HH:mm:ss}#)", DateTime.Now), conn);

It basically creates an SQL statement that looks like this: INSERT INTO Sheet1 VALUES (#05/29/2011 13:12:01#). Of course, I don't have to tell you how ugly this is. I'd much rather have a solution with a parameterized query.

Answer 1

It appears to be a known bug https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression

You might want to truncate the milisecond like this it appear to work for OleDbParameter:

DateTime org = DateTime.UtcNow;
DateTime truncatedDateTime = new DateTime(org.Year, org.Month, org.Day, org.Hour, org.Minute, org.Second);

And add this instead of the DateTime.Now into your parameter value.