Unable to update current time in a excel using oledb

Question

I am trying to insert current time to a Time column in the excel using the below code through an oledb connection but when I check the excel the value inserted is in Date format.

Value updated in excel - 1/0/1900 3:54:11 PM

Expected Value - 3:54:11 PM

         string currentTime = DateTime.Now.ToString("hh:mm:ss.fff tt");
         string cmnd1 = "Create Table [" + currentDate + "] (TestCase char(100), ExecutionTime Time, Result char(20))";
         string cmnd2 = "Insert Into [" + currentDate + "] (TestCase, ExecutionTime, Result) values ("+ "'" + tName + "',@dd,'" + result +"')" ;
         using (OleDbConnection conn = new OleDbConnection(ConnectionStringtd))
         {
             OleDbCommand createSheet = new OleDbCommand(cmnd1, conn);
             OleDbCommand insertResult = new OleDbCommand(cmnd2, conn);
             insertResult.Parameters.AddWithValue("@dd", DateTime.Now.TimeOfDay);
             conn.Open();
             try
             {
                 createSheet.ExecuteNonQuery();
             }
             catch(OleDbException) {}
             insertResult.ExecuteNonQuery();
         }

     }

Answer 1

AFAIK, when you enter pure time value stored as a datetime with the entered time portion, and a date part will be January 0, 1900 automatically since the days before 1900 are incorrect in Excel.

Instead of that, pass your DateTime.Now directly to parameter and change your column format type to Time with h:mm:ss tt format in your format cells part. By the way, you just paramterized @dd part. Use parameters for the other values that you try to insert. Don't concatenate them.

insertResult.Parameters.AddWithValue("@dd", DateTime.Now);

And don't use AddWithValue anymore. It may generate unexpected and suprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Also use using statement to dispose your commands as you did for the connection.