Time columns in OLE DB .NET Data Provider applications

You can insert data in time columns by binding time values to parameter markers. After you add the time values, you can retrieve the data using either the IDataRecord.GetValue() method or the OleDbDataReader.GetTimeSpan() method.

Inserting using parameter markers

You want to insert a time value into a Time column:

command.CommandText = "insert into mytable(c1) values( ? )";

where column c1 is a Time column. Here are two methods to bind a time value to the parameter marker:

Using OleDbParameter.OleDbType = OleDbType.DBTime

Because OleDbType.DBTime maps to a TimeSpan object, you must supply a TimeSpan object as the parameter value. The parameter value cannot be a String or a DateTime object, it must be a TimeSpan object. For example:

      p1.OleDbType = OleDbType.DBTime;
      p1.Value = TimeSpan.Parse("0.11:20:30");
      rowsAffected = cmd.ExecuteNonQuery();

The format of the TimeSpan is represented as a string in the format "[-]d.hh:mm:ss.ff" as documented in the MSDN documentation.

Using OleDbParameter.OleDbType = OleDbType.DateTime

This will force the OLE DB .NET Data Provider to convert the parameter value to a DateTime object, instead of a TimeSpan object, consequently the parameter value can be any valid string/object that can be converted into a DateTime object. This means values such as "11:20:30" will work. The value can also be a DateTime object. The value cannot be a TimeSpan object since a TimeSpan object cannot be converted to a DateTime object -- TimeSpan doesn't implement IConvertible.

For example:

      p1.OleDbType = OleDbType.DBTimeStamp;
      p1.Value = "11:20:30";
      rowsAffected = cmd.ExecuteNonQuery();

Retrieval

To retrieve a time column you need to use the IDataRecord.GetValue() method or the OleDbDataReader.GetTimeSpan() method.

For example:

TimeSpan ts1 = ((OleDbDataReader)reader).GetTimeSpan( 0 );
TimeSpan ts2 = (TimeSpan) reader.GetValue( 0 );