DateTime.ParseExact

Question

C# is kicking my butt... I have a text file I'm splitting in hopes to insert into SQL. I need a swift shove in the right direction!

An excerpt from the file I am capturing is below and I am splitting on " - "

2020-06-25-13.23.04.220000 - Running MRP for Site

I can split the two parts just fine. console_output

But can't seem to get the date into a format that is valid for my SQL insert. I think, but could be completely wrong that I need to reformat this date string using some REPLACE commands. If I try and use DateTime.ParseExact using my non-working code below I receive a System.FormatException:String was not recognized as valid on my DateTime.ParseExact line.

 foreach (string line in lines)
        {
            if (line.Contains("Running MRP for Site"))
            {
                List<string> s = new List<string>(
                line.Split(new string[] { " - " }, StringSplitOptions.None));
                Console.WriteLine(s[0].ToString());
                Console.WriteLine(s[1].ToString());
                string format = "yyyy-MM-dd-hh:mm:ss:ffffff";
                string date = s[0].ToString().Replace('.', ':');                   
                DateTime dt = DateTime.ParseExact(date, format, CultureInfo.InvariantCulture);
                /*
                if (conn.State != ConnectionState.Open)
                {
                    conn = new SqlConnection { ConnectionString = Properties.Settings.Default.ConnectionString };
                    conn.Open();
                }
                {
                    String query = @"INSERT INTO

You should not be concatenating the SQL with the date as a string. You should be [passing parameterized SQL](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) with a placeholder for the date, then passing in the date as a parameter, keeping it is a c# `DateTime`. This design mitigates the risk of SQL injection and also solves your problem without dealing with the formatting of anything. — John Wu, Jul 06 '20 at 21:26
Had similar issue last week. Had to use Trim() to remove the space before the dash. — jdweng, Jul 06 '20 at 21:27
Hours need to be HH since you have 13. The following works without replacing periods : string input = "2020-06-25-13.23.04.220000"; string format = @"yyyy-MM-dd-HH.mm.ss.ffffff"; DateTime date = DateTime.ParseExact(input, format, CultureInfo.InvariantCulture); You can also use just one H if times less than 10 are one digit. — jdweng, Jul 06 '20 at 21:36

Scott Hannen · Accepted Answer · 2020-07-06T21:44:59.597

It's the hh. That's a 12-hour clock. You need HH for a 24-hour clock. See Custom date and time format strings - the "HH" custom format specifier.

Here it is in the form of a unit test that takes the input string and verifies that the result is correct (although perhaps crudely.)

[TestMethod]
public void ParseDateTimeTest()
{
    var input = "2020-06-25-13.23.04.220000 - Running MRP for Site";
    var firstSegment = input.Split(new string[] { " - " }, StringSplitOptions.None)[0];
    string format = "yyyy-MM-dd-HH.mm.ss.ffffff";
    var parsed = DateTime.ParseExact(firstSegment, format, CultureInfo.InvariantCulture);
    Assert.AreEqual(13, Math.Truncate((parsed - new DateTime(2020, 6, 25)).TotalHours));
}

That was it. Amazing how the simple things do you in. Much appreciated! — Walker Edwards, Jul 06 '20 at 23:22

DateTime.ParseExact

1 Answers1