String was not recognized as a valid DateTime.

Question

I have received this exception when one of my column's data table have null value. Let say I do want to allow null value or something like this, how can I fix this?

String was not recognized as a valid DateTime.

This is my code.

     foreach (DataRow row in ds.Tables[0].Rows)
                {
     row["Effective_Period"] = Convert.ToDateTime(row["Effect_Date_From"].ToString()).ToString("dd/MM/yyyy") 
+ " - " +  Convert.ToDateTime(row["Effect_Date_To"].ToString()).ToString("dd/MM/yyyy");
    }

In my ds.Table, here is my column

----------------------
Effective_Period
---------------------
10/2/2012 - 20/3/2012
---------------------

---------------------

---------------------

Answer 1

Possible solution:

foreach (DataRow row in ds.Tables[0].Rows)
{
    DateTime effectiveDateFrom;
    DateTime effectiveDateTo;

    if (!DateTime.TryParse(row["Effect_Date_From"], out effectiveDateFrom)
        effectiveDateFrom = DateTime.MinValue;

    if (!DateTime.TryParse(row["Effect_Date_To"], out effectiveDateTo)
        effectiveDateTo = DateTime.MinValue;

    row["Effective_Period"] = effectiveDateFrom.ToString("dd/MM/yyyy") + " - " +  effectiveDateTo.ToString("dd/MM/yyyy");
}

Answer 2

You may want to reflect the nullable option in your C# representation of the DateTime definition:

Nullable<DateTime> value;
// or
DateTime? value;

Both forms are equivalent.

Ofcourse, when working with the value in C#, you have to define what to do on null and you can't rely on some magic null.ToString() for your conversions.

You may also want to have a look at this question. Essence: use row.Field<DateTime?>("Effect_Date_From") to get your nullable DateTime from a datarow.

Answer 3

If you want to allow NULL, then make the field as NULLABLE in your Database table. It will then allow NULL values to be inserted without any issues.

Also, if you want to allow a value such as you have shown, you will have to make the field as nvarchar since this wont be recognized as a valid DateTime.

Another important note, instead of storing like this, you could easily create two separate columns for the from-to period and store DateTime values in both respectively. That would also give you the advantage to query data and filter data as per the dates required.

UPDATE :

You will also receive this exception if you pass a NULL and use .ToString(). So make sure you are passing something here, be it a date or a string before using .ToString(). And on the backend, you can modify the data type as you prefer.

A good thing to do here would be implementing a null check before you convert using .ToString(). If its null, you can straightaway pass a null and if its not, you can convert the value and then pass that.

Hope this helps.

Answer 4

Using DateTime.TryParse can help you check if there is an invalid date, and twist your business logic accordingly.

https://msdn.microsoft.com/en-us/library/system.datetime.tryparse(v=vs.110).aspx

Answer 5

Try:

 foreach (DataRow row in ds.Tables[0].Rows)
            {
                bool fromIsNull = DBNull.Value.Equals(row["Effect_Date_From"]);
                bool toIsNull = DBNull.Value.Equals(row["Effect_Date_To"]);
                row["Effective_Period"] = (fromIsNull ? string.Empty : Convert.ToDateTime(row["Effect_Date_From"].ToString()).ToString("dd/MM/yyyy"))
                    + (fromIsNull || toIsNull ? string.Empty : " - " )
                    + (toIsNull ? string.Empty : Convert.ToDateTime(row["Effect_Date_To"].ToString()).ToString("dd/MM/yyyy"));
            }

updated the code to accommodate your last comment. that's how you'd hide/show the " - ". but that's all up to how you want to handle the case.

Answer 6

Null doesn't have a ToString() function so if you expect results to be null you have to make sure you won't call a function on it.