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.

In the past I didn't bother with it and just inserted DateTime.Now.ToString(). — MAW74656, Jun 06 '11 at 18:54

score 5 · Accepted Answer · answered Jun 07 '11 at 14:52

5

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.

answered Jun 07 '11 at 14:52

Dominic St-Pierre

2,429
3
27
35

Brilliant, thanks. I'll try that tomorrow and mark it as the answer if it worked. I never thought that this was a generic OLEDB bug (rather than an Excel ISAM specific thing)... – Heinzi Jun 07 '11 at 15:00

score 1 · Answer 2 · answered Jan 21 '14 at 14:04

1

The problem is the cell containing datetime value cannot be directly put into excel' column. You have to either insert the date component or the time component. The reason for failure is the default property of excel' cell is "values" instead of "datetime" in excel.

answered Jan 21 '14 at 14:04

Shubhojit

121
6

I disagree. Inserting a combined date/time value (after truncating milliseconds as recommended in Dominic's answer) works perfectly fine. – Heinzi Jan 21 '14 at 14:14
@Heinzi, I agree as I tried the earlier night by truncating the milliseconds, I could have adopted it had I known this earlier. – Shubhojit Jan 22 '14 at 07:00

Excel Jet OLE DB: Inserting a DateTime value

2 Answers2

Linked