System.FormatException: 'String '"2022-04-14 13:03:12"' was not recognized-CodePudding

cnn.Open();
using (StreamReader reader = new StreamReader(@"C:/Users/jhogan/Documents/Test Folder/test_import.csv"))
                {
                  while (!reader.EndOfStream)
                  {
                      var line = reader.ReadLine();
                      if (lineNumber != 0)
                      {
                         var values = line.Split(',');
                         var date = values[0];
                         var sentDate = DateTime.ParseExact(date, "yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture);
                    
                         await cnn.ExecuteAsync(insertFeedback, new { 
       @SentDate = sentDate, @RatedDate = values[1], @Rating = values[2], @Location = values[3], @Notes = values[4], @Email = values[5], @Feedback = values[6] },commandTimeout: 120,commandType: CommandType.StoredProcedure);}

I am attempting to load this data from a csv file into my database, but am running into the following error from the title. The original formatting of the date from my csv is HH:MM:SS PM/AM and my column type in the database is datetime. In my database, the datetime formatting is YYYY-MM-DD HH:MM:SS:MMM. I think this is because of the database's formatting of things, but I am genuinely not sure.

CodePudding user response：

my column type in the database is datetime. In my database, the datetime formatting is YYYY-MM-DD HH:MM:SS:MMM.

You misunderstand how the database and .Net DateTime values work. If the column type or data type is DateTime, the format in the database or in memory is binary, and when you see YYYY-MM-DD HH:MM:SS:MMM that's just a convenience provided for you by your tooling. There is no need to ever try to match your string to that internal binary format.

But as for the code, the problem is the lower-case hh in the format string. We can see in the documentation the lower-case h is for 12-hour times and upper-case H is for 24 hour times. Looking at the input string 2022-04-14 13:03:12 from the error in the question title, we have a 13 in this position, which definitely puts you in upper-case H territory:

var sentDate = DateTime.ParseExact(date, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);

But it's worse than that. I also see this in a comment:

the first row is 4/14/2022 1:03:12 PM

So first of all, the format string for DateTime.ParseExact() tells the format of the original input source string, not the target format for the parsed value, which is, after all, binary and not human readable anyway.

The format string to parse that value would look like this: M/dd/yyyy h:mm:ss tt. Also note the use of paired vs single characters in certain positions.

var sentDate = DateTime.ParseExact(date, "M/dd/yyyy h:mm:ss tt", CultureInfo.InvariantCulture);

But the real significance here is the variation between the error in the question title and the comment means you might have a mix of formats. That's scary. DateTime.ParseExact() uses strict parsing. If the input does not EXACTLY match the expected format you WILL get an exception.

Fortunately, there is an overload for the method that accepts multiple format strings. Even with this overload, though, you probably need to take the time to review the csv data and make sure that every possible format in the data is accounted for.

And if data for this field could be entered by random humans may God have mercy on your soul.