CODEWITHSUNDEEP
c#sql-serverdatetime
Leia
Leia

Reputation: 357

Unable to convert DateTime from SQL Server to C# DateTime

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:

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

Upvotes: 4

Views: 1736

Answers (2)

Cato
Cato

Reputation: 3701

Depending on your actual format, you can define a suitable format list and do a conversion like below

string[] mfs = { "MM/dd/yyyy HH:mm:ss",  "MM/dd/yyyy h:mm:ss tt"};

var dat = DateTime.ParseExact("04/23/1945 8:45:22 PM", mfs, 
                System.Globalization.CultureInfo.InvariantCulture, 
                System.Globalization.DateTimeStyles.None);

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062520

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.

Upvotes: 7

Related Questions