Unable to convert DateTime from SQL Server to C# DateTime

Question

I wrote a program in C# that uses dates. It takes the value from a SQL Server table. I was using Windows 7 and the program worked fine. I had this line of code:

DateTime fechaOperacion = Convert.ToDateTime(reader["FechaOperacion"]);

The reader returned a date in a 24h format and I was able to convert that to a DateTime variable.

Now I did a system upgrade to Windows 10 and that same line of code is throwing the following error:

String was not recognized as a valid DateTime. there is an unknown word starting at index 20.

And now the reader returns a.m. / p.m. format, and at index 20 there is a.m or p.m.

I have tried the following things:

• Rephrasing the line of code to:

  1. Convert.ToDateTime(reader["FechaOperacion"], System.Globalization.CultureInfo.InvariantCulture)
  2. reader.GetDateTime(reader.GetOrdinal("FechaOperacion"));

• Convert the culture to 24h format

  System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo(1033);
  

But none of that seems to work, I don't know what else to do.

Answer 1

Ultimately, the underlying problem here is storing a value that represents a date/time as textual data (meaning, some kind of [n][var]char({max|n}), or at a push: [n]text). This has multiple problems:

• it takes more space
• it cannot be sorted correctly / efficiently
• it cannot be indexed correctly / efficiently
• it cannot be filtered correctly / efficiently
• it leads to parsing errors between client and server
• it has all sorts of localization and internationalization problems

What you should have is a datetime / date / time / etc column. This is then stored as a number (not a string) that requires zero parsing and will work reliably without any conversion problems.

---

Note: it could be that you are storing it correctly but formatting it inside the select statement of your query. In which case, just don't do that; return the date-time raw, and let the receiving client worry about how to display it.