datetime format in ASP.NET C# not recognized

Question

I have a column in my database with the following content:

status
2018-12-31 15:31:56.000 (result of a select in SMSS)

I am trying to get this column's data in ASP.NET code behind page through a query in C#:

var connectionString = System.Configuration.ConfigurationManager.AppSettings["connection"];
SqlConnection con = new SqlConnection(connectionString);

DataTable dt = new DataTable();            

SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 1 [status] from [tablename] where idNo = '" + idNo+ "'", con);

con.Open();
da.Fill(dt);

if (dt.Rows.Count > 0)
{
    Debug.WriteLine("\n Rows extracted."); // -> Error thrown here.

    Debug.WriteLine("\n Rows content:" + DateTime.ParseExact(dt.Rows[0][0].ToString(), "yyyy-MM-dd hh:mm:ss.000", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None));

    con.Close();
    da.Dispose();
}

No matter what I try I always get either

String was not recognized as a valid DateTime

or the datetime is not recognized as part of the Gregorian calendar.

I have tried to display the dt.Rows[0][0] content directly, but then I get an error about the date not being in the Gregorian calendar.

Are there any steps I could undertake to understand what is going on with this?

Working with DateTime is an absolute nightmare in C#, I wished MS would finally fix this.

Please don't point me to the docs or other articles, that's obviously where I come from...

*"Working with DateTimes is an absolute nightmare in C#, I wished MS would finally fix this."* MS won't fix it, but [Jon Skeet and some others did: NodaTime](https://nodatime.org/) — Ron Beyer, Apr 11 '18 at 03:09
What is displayed if you simply do `Console.WriteLine(dt.Rows[0][0].ToString());` what is the type of status column in database table? — Chetan, Apr 11 '18 at 03:10
First thing. Stop concatenating strings for SQL queries, you are wide open for SQL injection attacks. Secondly, you should consult the documentation and learn what the actual date time format strings are (you will soon see your problem). Lastly, why do you want to parse exact anyway? You can just use DateTime.TryParse() and it will accept reasonably formatted datetime strings pretty well. — maccettura, Apr 11 '18 at 03:13
It doesn't make sense for that line to throw an exception (or does that mean it's thrown _after_ that line?). It would be helpful to step through this code in a debugger and use that to inspect both the value and type of `dt.Rows[0][0]` and add that information to the question. Also, a side note, but you'll want to move `con.Close(); da.Dispose();` outside of the `if` block so those resources are closed even if no rows are returned. Better yet, use `using` blocks to manage `con` and `da`. — Lance U. Matthews, Apr 11 '18 at 03:37
@RonBeyer: Thanks for that, I will check it. We have a lot of issues with datetime here! — Just_Stacking, Apr 11 '18 at 03:57
If the `status` column is a `datetime` and not text then you don't need to parse it in the first place. `DateTime status = (DateTime) dt.Rows[0][0];` will give you the value directly. — Lance U. Matthews, Apr 11 '18 at 04:07
Your recent edit has completely changed the nature of your original question! There is now nothing to do with parsing date time. If you have a new question, please ask a new question, don't completely change and existing question. As it stands, my answer now has very little to do with the current version of the question. "Error thrown here" Is it the same error? If so that error makes no sense. If it's a new error, what is it? — Jon P, Apr 11 '18 at 04:16
Both answers you currently have are good, but both overlooked another problem in your code - Don't use a DataAdapter and a DataTable to get a single value out of an SQL Query - instead, use `command.ExecuteScalar`. — Zohar Peled, Apr 12 '18 at 07:12
@JonP: You are right, definitely not my best work on SO... Tried for a long time and got tired. @ Zohar Peled: Thanks will try this out. Due to the problems I usually encounter I try to stick with "what works". Definitely need to learn more :.[ — Just_Stacking, Apr 12 '18 at 08:56

Jon P · Accepted Answer · 2018-04-11T04:08:40.043

Case is important when it comes to format strings. See: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

The string you actually want is:

yyyy-MM-dd HH:mm:ss.fff

Note the case of Months, Hours and minutes.

h = 12 hour clock, H = 24 hour clock.

Demo

Now you just need to adopt best practices for calling the database. Use parameterised queries and read up on using statements

Sankar · Answer 2 · 2018-04-11T03:13:55.840

2

You should use MM(uppercase) which means month and the mm(lowercase) is minute.

 if (dt.Rows.Count > 0)
        {
          Debug.WriteLine("\n Rows extracted."); // -> Error thrown here.

          Debug.WriteLine("\n Rows content:" + DateTime.ParseExact(dt.Rows[0][0].ToString(), "yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None));

          con.Close();
          da.Dispose();
        }

NOTE: Please DON'T concatenate data in your query, That will be the invitation to the hacker for SQL injection. Use parameterized queries instead

More info : https://stackoverflow.com/a/7505842/2131576

edited Apr 11 '18 at 03:13

answered Apr 11 '18 at 03:05

Sankar

6,908
2
30
53

Stupid enter key connected to submit... Thanks for your answer. I already tried MM also not working. BTW I found the weird mi format here: [link]https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql search for 120. I tried working with parameters but couldn't get that to work. For now the main issue is with the datetime. I have been blocked on this for days now. – Just_Stacking Apr 11 '18 at 03:12
What prints this `Debug.WriteLine(dt.Rows[0][0].ToString());` – Sankar Apr 11 '18 at 03:14
Does not even get there. Error thrown before. – Just_Stacking Apr 11 '18 at 03:15
1

@Just_Stacking That documentation is for T-SQL. You want .NET (specifically, [Custom Date and Time Format Strings](https://learn.microsoft.com/dotnet/standard/base-types/custom-date-and-time-format-strings). – Lance U. Matthews Apr 11 '18 at 03:17
@Just_Stacking No you change your code like that and test it then let me know the result. – Sankar Apr 11 '18 at 03:18
I updated both my source and this question. Still failing as marked in the line where I try to print "We have rows." – Just_Stacking Apr 11 '18 at 03:24
@BACON thanks, any ideas how I could understand why it fails on the dt.Rows? It does not even want to output the content and fails before as marked in the source (// -> Error thrown here.). – Just_Stacking Apr 11 '18 at 03:28
@Just_Stacking if it fails on the `dt.Rows` it means `dt == null`. Meaning `da.Fill(dt)` may not be setting the DataTable or problems with the connection. Could you add `if (dt == null) Debug.Write("Error")`. Tell us how it went – Ricardo González Apr 11 '18 at 03:39
Debug.WriteLine("rows content: " + dt.Rows[0]); results in this: rows content: System.Data.DataRow – Just_Stacking Apr 11 '18 at 03:53
@Just_Stacking should be `dt.Rows.Count` – Ricardo González Apr 11 '18 at 03:54
I found the other issue. `15:31:56.000` is 24-hour time, whereas `hh` is 12-hour. You want `HH` to parse 24-hour time. Also, consider changing `.000` to `.fff` so fractional seconds get parsed, too. – Lance U. Matthews Apr 11 '18 at 03:56

datetime format in ASP.NET C# not recognized

2 Answers2