Sum hours from different formatted dates. C#

Question

I have a problem. I need to sum hours worked in an office in a code. The dates i get from SQL server thats no problem but i have different formats. For example: 2019. 09. 23. 14:54:23, 2019.09.23 14:54:23 or 2019-09-23 14:54:23; And i want to sum hours worked in result. No matter the year. Heres the example:

try
        {

            string betölt = "SELECT * from munkaorak where";
            if (cbTech.Text != "")
            {
                betölt += " Munkaszam='" + cbMunka.Text + "' AND Részfolyamat='" + cbRész.Text + "' AND TechKod='" + cbTech.Text + "'";
            }
            else if (cbRész.Text != "")
            {
                betölt += " Munkaszam='" + cbMunka.Text + "' AND Részfolyamat='" + cbRész.Text + "'";
            }
            else if(cbMunka.Text !="")
            {
                betölt += " Munkaszam='" + cbMunka.Text + "'";
            }
            betölt += " order by ID DESC";
            MySqlCommand name = new MySqlCommand(betölt, kapcsolat);
            kapcsolat.Open();
            olvasó = name.ExecuteReader();
            int összora = 0;

            if (olvasó.HasRows)
            {
                while (olvasó.Read())
                {
                    if (olvasó.GetString(7) != "Befejezés: ")
                    {
                        string[] aha = olvasó.GetString(6).Split(' ');
                        string kezdes = aha[4];
                        string[] kezd = kezdes.Split(':');
                        int kezdoido = Convert.ToInt32(kezd[0]) * 60 * 60 + Convert.ToInt32(kezd[1]) * 60 + Convert.ToInt32(kezd[2]);

                        int befejezoido = 0;
                        string aha22 = "";
                        if (olvasó.GetString(7).IndexOf('-') >= 0)
                        {
                            string[] aha2 = olvasó.GetString(7).Split(' ');
                            string befejezes = aha2[1];
                            string[] bef = befejezes.Split(':');
                            aha22 = aha2[0].Split('-')[2];
                            befejezoido = Convert.ToInt32(bef[0]) * 60 * 60 + Convert.ToInt32(bef[1]) * 60 + Convert.ToInt32(bef[2]);
                        }
                        else
                        {
                            string[] aha2 = olvasó.GetString(7).Split(' ');
                            string befejezes = aha2[4];
                            string[] bef = befejezes.Split(':');
                            aha22 = aha2[3];
                            befejezoido = Convert.ToInt32(bef[0]) * 60 * 60 + Convert.ToInt32(bef[1]) * 60 + Convert.ToInt32(bef[2]);
                        }                          

                        string dolgozott = "";
                        if (aha[3].Replace(".", "") == aha22.Replace(".", ""))
                        {
                            dolgozott = mpbolora(befejezoido - kezdoido);
                            összora += befejezoido - kezdoido;
                        }
                        else
                        {
                            dolgozott = mpbolora((86400 - kezdoido) + befejezoido);
                            összora += (86400 - kezdoido) + befejezoido;
                        }
                        string validalo = "";
                        try
                        {
                            string[] validal = olvasó.GetString(9).Split(' ');
                            validalo = validal[0] + " " + validal[1] + " " + validal[2] + validal[3] + validal[4] + " " + validal[5];
                        }
                        catch
                        {
                            validalo = olvasó.GetString(9);
                        }
                        string munkafolyamat = olvasó.GetString(3) + "-" + olvasó.GetString(4) + "-" + olvasó.GetString(5);
                        string[] sorok = { olvasó.GetString(2), dolgozott, olvasó.GetString(6).Replace("Kezdés: ", ""), olvasó.GetString(7).Replace("Befejezés: ", ""), olvasó.GetString(8), validalo, munkafolyamat };
                        var lv = new ListViewItem(sorok);
                        lvStat.Items.Add(lv);
                    }


                }
            }
            else
            {
                kapcsolat.Close();
                MessageBox.Show("Nincs adat!", "Figyelem");
            }
            kapcsolat.Close();
            lblÖssz.Text = "Összesen ledolgozott órák: " + mpbolora(összora);
        }
        catch (Exception a)
        {
            MessageBox.Show(a.Message);
            kapcsolat.Close();
        }
        kapcsolat.Close();

It worked but when different formats appeared its not working because '-' or spaces. Please help!

Answer 1

In C#, there is a bunch of methods provided to convert strings that contain date times in many formats into a unified DateTime object. These methods can recognize quite a few standard date time formats, and if yours differ from them, you can even provide your own.

• DateTime.Parse() - Converts a string to a DateTime object. If operation fails, it'll thrown an exception.
• DateTime.TryParse() - Converts a string to a DateTime object only if possible. Returns true if successful, and false if it fails.
• DateTime.TryParseExact() - Converts a string that is in the specified format into a DateTime object. Returns true if successful, and false otherwise.

In your case, you can use DateTime.TryParse() (which is recommended over simply using DateTime.Parse() unless you're absolutely sure the format is correct) like so:

var dtStr1 = " 2019. 09. 23. 14:54:23";
var dtStr2 = "2019.09.23 14:54:23";
var dtStr3 = "2019-09-23 14:54:23";

DateTime.TryParse(dtStr1, out DateTime dt1);
DateTime.TryParse(dtStr2, out DateTime dt2);
DateTime.TryParse(dtStr3, out DateTime dt3);

Once converted to a DateTime object, it no longer has a format associated with it. It's a structure, and hence only has member variables and methods. So to calculate total hours etc. you can use provided methods.

Say you want to calculate time between day's work start and end. You can convert those into DateTime objects, then subtract one from the others which will give you a TimeSpam object.

var dtStrStart = "2019.09.23 08:23:12";
var dtStrEnd = "2019.09.23 16:17:28";

DateTime.TryParse(dtStrStart, out DateTime dtStart);
DateTime.TryParse(dtStrEnd, out DateTime dtEnd);

var diff = dtEnd - dtStart;

Now the TimeSpan object, which is diff here, will give you a bunch of properties with difference in hours, minutes etc.

The TimeSpan.Days, TimeSpan.Minutes etc will give you the time in days, minutes etc.

Console.WriteLine(diff.Days);
Console.WriteLine(diff.Hours);
Console.WriteLine(diff.Minutes);
Console.WriteLine(diff.Seconds);
Console.WriteLine(diff.Milliseconds);

Output:

0

7

54

16

0

The TimeSpan.TotalMinutes etc will give you the entire time period in respective units.

Console.WriteLine(diff.TotalDays);
Console.WriteLine(diff.TotalHours);
Console.WriteLine(diff.TotalMinutes);
Console.WriteLine(diff.TotalSeconds);
Console.WriteLine(diff.TotalMilliseconds);

Output:

0.329351851851852

7.90444444444444

474.266666666667

28456

28456000

And conversely, when you're storing data in the database, you must again use a standard format, such as datetime or datetime2. It's advised you use datetime2, more info here.

Answer 2

Your code should look more like this:

    try
    {

        MySqlCommand name = new MySqlCommand("SELECT * from munkaorak WHERE Munkaszam=@m", kapcsolat);
        name.Parameters.AddWithValue("@m", cbMunka.Text);

        if (cbRész.Text != "")
        {
            name.CommandText += " AND Részfolyamat=@r";
            name.Parameters.AddWithValue("@r", cbRész.Text);
        }

        if (cbTech.Text != "")
        {
            name.CommandText += " AND TechKod=@t";
            name.Parameters.AddWithValue("@t", cbTech.Text);
        }

        name.CommandText += " order by ID DESC"; //is it really necessary?
        MySqlDataAdapter da = new MySqlDataAdapter(name);

        DataTable dt = new DataTable();
        da.Fill(dt);


        foreach(DataRow ro in dt.Rows){

          string fromStr = ro["YOUR_FROM_DATE_COLUMN_NAME"].ToString();

          //cope with dates in varying formats
          //by replacing all non-numeric chars with nothing
          fromStr = Regex.Replace(fromStr, @"[^0-9]", "");

          //now our dates of [2019. 09. 23. 14:54:23], [2019.09.23 14:54:23] or [2019-09-23 14:54:23]
          //just become 20190923145423
          DateTime fromDt = DateTime.ParseExact(fromStr, "yyyyMMddHHmmss", CultureInfo.InvariantCulture);


          string toStr = ro["YOUR_TO_DATE_COLUMN_NAME"].ToString();

          toStr = Regex.Replace(toStr, @"[^0-9]", "");

          DateTime toDt = DateTime.ParseExact(toStr, "yyyyMMddHHmmss", CultureInfo.InvariantCulture);


          //total hours worked
          (toDt - fromDt).TotalHours;


        }
   }

Hopefully that looks a lot simpler

Here you see no..:

• Risky SQL injection hack possibility - don't concatenate values into your SQL, ever. Always concatenate a parameter in and then give a value to the parameter. Always
• Difficult to read, lengthy string concatenation - looks terrible, always avoid it if you can
• DB Connection opening and closing - micromanaging the database connection isn't necessary when using a dataadapter because it opens and closes for you
• DataReader code full of magic numbers - GetString(7), hmmm.. was that the time in or time out? GetInt(4) - was it the age? The year? Here we get rid of all the datareader GetXX calls with their column ordinals and fill a DataTable (something like a 2D array) with rows that can be indexed by string names. It's still not as good as it can be (strongly typed DataTables are better) but it's a huge leap better than filling code with magic numbers, and working with everything in the most obscure, weakly typed way possible
• Awkward time handling - it's gone in favour of Date parsing, because pulling strings to bits number by number, converting them to int, multiplying them by seconds and hours so they can be manipulated is tedious and hard to read - do away with it all by parsing these strings to the data types that they should have been stored as in the first place; you need to record the date and times that things happen at. Try your best to get that DB converted so these things are stored properly, and until then convert your strings to DateTime
• Diffing dates using seconds: utilising TimeSpan calculations means no need to convert things to seconds, do crude math, drop all notions of time zones, or daylight savings changes etc; by using dates subtracted from each other you get a time period between those dates that takes things like daylight saving clock changes into account. Or even the ability to have one date that is tomorrow, or X days into the future. Might not matter for this app, but one day it could..

If you have MySQL 8 you can do the regex replace in the DB. Could even get the DB to diff and sum the dates.. We can't really make any recommendations on this point though because we don't know the column names