Convert specific string to dateTime in dataTable Row

Question

I am trying to convert a specific String to a DateTime which is contained in a DataTable. But for some reason not quite working. I have tried a few combinations. The thing is that the string actually contains " " surrounding the date.

So cell in DataTable containing value (including double quotes) "2014-08-08 08:00:00"

My code iterates through all of it and tries to convert it into a DateTime (need it as doing a bulkCopy.WriteToServer(datatable) to SQL Server.

  //Fix up default values and remove double quotes from field in order to convert to date
  if(dt.Rows.Count > 0)
  {
      for (int i = 0; i < dt.Rows.Count; i++)
      {
          dt.Rows[i]["usagestartdate"] = 
              dt.Rows[i]["usagestartdate"].ToString() == "" ?
              DateTime.Now.AddYears(-2014) : 
              Convert.ToDateTime((dt.Rows[i]["invoiceid"].ToString().Replace("'", "")));
          dt.Rows[i]["usagestartdate"] = 
              dt.Rows[i]["usageend"].ToString() == "" ? 
              DateTime.Now.AddYears(-2014) : 
              Convert.ToDateTime(dt.Rows[i]["usageend"].ToString());
      }
  }

I get the error on the first line inside the loop.

String was not recognized as a valid DateTime.

Any ideas how to make the cast work properly?

Answer 1

You should be using

DateTime.ParseExact(dtRows[i], "yyyy/MM/DD HH:mm:ss", CultureInfo.InvariantCulture);