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.

Define "it does not seem to work". In particular, what's the result of reader.GetDateTime(reader.GetOrdinal("FechaOperacion")), which is the correct way to read a DATETIME column independently of any format? — Jeroen Mostert
– Jeroen Mostert, Commented Sep 29, 2016 at 13:58
What exactly is reader["FechaOperacion"] returning? the fact that it mentions String means that it isn't "sql DateTime" - a SQL datetime value would be fine and doesn't need to be parsed. So: what is it? — Marc Gravell
– Marc Gravell, Commented Sep 29, 2016 at 13:59
If FechaOperacion is a DATETIME column, then it sounds like it's a display issue. What happens if you do a var dateStr = fechaOperacion.ToString("dd/MM/yyyy HH:mm:ss");? What is the value of dateStr? — Howwie
– Howwie, Commented Sep 29, 2016 at 13:59
You don't have to convert a SQL datetime to a C# DateTime. They are equivalent. If you think you need to convert anything, it's because you are using strings instead of datetime. Instead of using Convert you shuould be able to use a direct cast, ie (DateTime). If you can't you are returning strings — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 29, 2016 at 14:00
From what I read in your question I'm understanding that you are storing your date in nvarchar/varchar in the db which is not a good idea. If you are using datetime, you don't need to convert it. — mybirthname
– mybirthname, Commented Sep 29, 2016 at 14:00

Marc Gravell · Accepted Answer · 2016-09-29 14:02:42Z

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.

marc_s · Accepted Answer · 2016-09-29 14:57:10Z

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);

Collectives™ on Stack Overflow

Unable to convert DateTime from SQL Server to C# DateTime

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related