Cannot cast DBNull.Value to type System.DateTime

Question

I have had to pull two separate queries of data from separate databases and now I am trying to merge then into one datatable in C#. But I am getting an error whenever I have null values coming from a column containing mostly datetime values. I just can't seem to find out what I can do to fix it. i have tried if statements to catch the null values etc but keep getting exceptions.

I am getting the following error:

System.InvalidCastException was unhandled
HResult=-2147467262
  Message=Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type.
  Source=System.Data.DataSetExtensions
  StackTrace:
       at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)
       at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName)
       at HueniData.Program.Main(String[] args) in c:\Visual Studio Projects\HueniData\HueniData\Program.cs:line 60
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

for the following code:

var resultTable = from t1 in actualTable.AsEnumerable()
                  join t2 in recipeTable.AsEnumerable()
                    on t1.Field<int>("Step No") equals t2.Field<int>("Step No")
                  select new { t1, t2 };


DataTable newTable = new DataTable();
newTable.Columns.Add("StepNo", typeof(int));
newTable.Columns.Add("ActualDuration", typeof(decimal));
newTable.Columns.Add("RecipeDuration", typeof(decimal));
newTable.Columns.Add("StartTime", typeof(DateTime));


DataRow newRow;
foreach (var dr in resultTable)
{
    newRow = newTable.NewRow();
    newRow["StepNo"] = dr.t1.Field<int>("Step No");
    newRow["ActualDuration"] = dr.t1.Field<decimal>("ActualDuration");
    newRow["RecipeDuration"] = dr.t2.Field<decimal>("RecipeDuration");
    **newRow["StartTime"] = dr.t1.Field<DateTime>("Step Start Time");**                                           
    newTable.Rows.Add(newRow);
}

UPDATE: The below answers were helpful but I have basically attempted to do both those originally using various if statements and changing the datetime to datetime?. I still get the error. The way I resolved this was by using:

 newTable.Columns.Add("StartTime", typeof(Object));
newRow["StartTime"] = dr.t1.Field<Object>("Step Start Time");

But if anyone can explain to me what the problem was I would still love to know.

Answer 1

Make your DateTime column nullable. You are trying to cast null value to DateTime which will throw exception. Change following code

newTable.Columns.Add("StartTime", typeof(DateTime?));

And when adding rows in DataTable change following line

newRow["StartTime"] = dr.t1.Field<DateTime?>("Step Start Time");

Answer 2

You do not need to use the Field<>() method when copying the data over. You can just copy the value directly without any casting:

newTable.Columns.Add("StartTime", typeof(DateTime));

....

newRow["StartTime"] = dr.t1["Step Start Time"];

Note: The DataColumn class has a AllowDBNull property, it is not designed to be used with Nullable<> types. When creating your new columns you may want to consider if you need to set AllowDBNull and any other properties relevant to your data.

What us happing with your code is Field<>() is designed to map a nullable DateTime column to the C# equivalent DateTime?. The DataRow object stores the values weakly typed, as a plain object, either a DateTime value or a DBNull value.

To make Field<>() work you would have to do something like this:

// Note column type is DateTime not DateTime?
newTable.Columns.Add("StartTime", typeof(DateTime));

....

if (dr.t1.Field<DateTime?>("Step Start Time") != null) {
    newRow["StartTime"] = dr.t1.Field<DateTime>("Step Start Time");
} else {
    // This is not necessary if the DataColumn's default value is DBNull
    newRow["StartTime"] = DBNull.Value;
}