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?

Just a sidenote: DateTime.Now.AddYears(-2014) looks... interesting. Why not just use DateTime.MinValue? — Joonas Koski, Dec 16 '14 at 18:34

score 1 · Answer 1 · edited Dec 16 '14 at 19:49

1

You should be using

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

edited Dec 16 '14 at 19:49

Soner Gönül

97,193
102
206
364

answered Dec 16 '14 at 18:28

R Quijano

1,301
9
10

That's not works. First, it is `dt.Rows[i]["invoiceid"]`, not `dtRows[i]`. Second, the string rounded with double quotes, that's why you need to change your format. Third, there is no `DD` as a format. Fourth, date separator is `-` not `/` in his example. – Soner Gönül Dec 16 '14 at 19:51

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You are mixing single quote(') with double quote(").

That's why when you write Replace("'", "") part, it doesn't replace with anything because your string doesn't have any single quotes. That's the reason one solution might be change from Replace("'", "") to Replace("\"", "") in your code.

But here an another way

Since you are using Convert.ToDateTime(string) overload, this method uses your CurrentCulture settings by default. And none of culture have a standard date and time format with double quoted string as "yyyy-MM-dd HH:mm:ss". That's why, you need to use custom date and time parsing with DateTime.TryParseExact method for example like;

string s = "\"2014-08-08 08:00:00\"";
string format = "\\\"yyyy-MM-dd HH:mm:ss\\\"";
DateTime date;
if(DateTime.TryParseExact(s, format, CultureInfo.InvariantCulture,
                          DateTimeStyles.None, out date))
{
    Console.WriteLine (date);
}

Here a demonstration.

score 0 · Answer 3 · edited Dec 16 '14 at 18:20

0

Just realised I did a Ctrl + Z and was still not covering the double quotes, just single. Replaced with

dt.Rows[i]["usagestartdate"] = dt.Rows[i]["usagestartdate"].ToString() == "" ? 
                               DateTime.Now.AddYears(-2014) : 
                               Convert.ToDateTime((dt.Rows[i]["usagestartdate"].ToString().Replace("\"", "")));

And all working now!

edited Dec 16 '14 at 18:20

crthompson

15,653
6
58
80

answered Dec 16 '14 at 18:19

user2091936

546
2
7
28

Convert specific string to dateTime in dataTable Row

3 Answers3

But here an another way