Get SqlDateTime overflow in SqlBulkCopy.WriteToServer()

Question

Dear guys

I insert data from a typed dataset into my MSSQL database by using SqlBuldCopy class:

foreach (DataTable dt in ds.Tables)
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) 
    {
        bulkCopy.DestinationTableName = "dbo." + dt.TableName + "_neu";

        try
        {
            bulkCopy.WriteToServer(dt);
        }
        catch (Exception ex)
        {
            throw new FaultException("\n" + dt.TableName + ": " + ex.Message);
        }
    }
}

It works great. But when I insert DataTime.MinValue into my database i get this error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Is there a way to say in the foreach something like this: if DateTime field value from dataset is DateTime.MinValue, don't insert the DateTime field from dataset into my database?

Best regards

Answer 1

I think you'd need to ensure there are no DateTime.MinValue's in the datatable - do you control the population of that table?

If so, instead of storing DateTime.MinValue in a DateTime field, use SqlDateTime.MinValue instead. Or, store DBNull in the datatable instead if you want NULL to be stored in to the DB.

Answer 2

If you've got SQL Server 2008 and don't mind storing the MinValue you can get round this by using DateTime2.