How to convert total time to minutes

Question

This was the error msg im getting enter image description hereI have a database that has a row "Total_Time" (time). It is in the format HH:MM:SS. I need the code to convert the "Total_time" to minutes.

For example, if Total_time = 01:30:00, the answer should be Total_minutes = 90, and I want to multiply the total_minutes with "Other" (int variable).

Below is what I have tried:

  Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click


    con = New System.Data.SqlClient.SqlConnection
    Try


        con.ConnectionString = "Data Source=Vicky-pc\sqlexpress;Initial Catalog=customer_details;Integrated Security=True;Pooling=False"
        con.Open()


        Dim cm As SqlClient.SqlCommand
        cm = New SqlClient.SqlCommand("SELECT * FROM customer_details WHERE Id=@id", con)
        cm.Parameters.AddWithValue("@id", TextBox5.Text)
        dr = cm.ExecuteReader()
        While dr.Read()
            Dim tt As Double
            tt = dr("Total_Time").ToString
            Dim other As Double
            other = dr("Other").ToString

            Dim str() As String
            Dim strmin As Double
            str = Split(tt.ToString, ":")
            strmin = (CDbl(str(1)) * 60 + CDbl(str(2)) + CDbl(str(3)) / 60).ToString

            Dim total As Decimal
            total = strmin + other
            Label7.Text = total.ToString
        End While
    Catch ex As Exception

    End Try
End Sub

but when i click nothing is happening label7 is not displaying any values Thanks in advance.

i have inserted this code for a button and when i click nothing is happening label7 is not displaying any values — , Feb 19 '17 at 14:53
what format is Total_Time really stored as in the DB? Why all the string conversions? — Trevor_G, Feb 19 '17 at 15:07
i need to display the total in the label7.text on the button click — , Feb 19 '17 at 15:09
Are you getting results back from your query? You haven't explained what debugging you have done and where the problem is. I would hope that you have at least verified whether your datareader contains any data and thus whether you are going into the loop or whether an exception is being thrown - if so you should definitely tell us what exception is being thrown... — Chris, Feb 19 '17 at 15:16
Turn on Option Strict `tt = dr("Total_Time").ToString` wont compile if `tt` is defined as double — Ňɏssa Pøngjǣrdenlarp, Feb 19 '17 at 15:51
`While dr.Read() Dim Total_Time As DateTime = Convert.ToDateTime(dr("Total_Time")) Dim minutes As Double = Total_Time.TimeOfDay.TotalMinutes Dim Other As Double = Val(dr("Other")) Dim total As Double = minutes * Other Label7.Text = total.ToString End While Catch ex As Exception` — , Feb 19 '17 at 15:56
You are erroring out. break on the Catch statement and see what the error is. BUt it looks like your Other value is not a number which would cause it to drop out. — Trevor_G, Feb 19 '17 at 16:32
you ALWAYS should test for dbnull when using databases. DBNull has some weird behaviours in math and Boolean comparisons. — Trevor_G, Feb 19 '17 at 16:34
is the value of "other" is just minutes i.e. integer or in the HH:MM:SS format ? . I have considered HH:MM:SS in my code. — Mukul Varshney, Feb 19 '17 at 16:35

Slai · Accepted Answer · 2018-01-10T15:52:29.633

2

Dim Total_minutes As Double = CDate("1:23:45").TimeOfDay.TotalMinutes          ' 83.75

To avoid similar errors, I would highly recommend using Option Strict

Dim Total_Time As DateTime = Convert.ToDateTime(dr!Total_Time)
Dim Total_minutes# = Total_Time.TimeOfDay.TotalMinutes

Dim Other# = Val(dr!Other)
Dim total# = Total_minutes * Other

Label7.Text = total.ToString

edited Jan 10 '18 at 15:52

answered Feb 19 '17 at 15:11

Slai

22,144
5
45
53

I'm curious - why are you answering if you didn't read the question? – Chris Feb 19 '17 at 15:14
@Chris because I read the title, and might be helpful to others that search for something similar – Slai Feb 19 '17 at 15:15

Mukul Varshney · Answer 2 · 2017-02-19T16:32:03.660

Try below

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

    con = New System.Data.SqlClient.SqlConnection
    Try
        con.ConnectionString = "Data Source=Vicky-pc\sqlexpress;Initial Catalog=customer_details;Integrated Security=True;Pooling=False"
        con.Open()

        Dim cm As SqlClient.SqlCommand
        cm = New SqlClient.SqlCommand("SELECT * FROM customer_details WHERE Id=@id", con)
        cm.Parameters.AddWithValue("@id", TextBox5.Text)
        dr = cm.ExecuteReader()
        While dr.Read()     
            Dim other As TimeSpan           
            Dim tt As TimeSpan

            If TimeSpan.TryParse(dr("Total_Time"), tt) Then
                If TimeSpan.TryParse(dr("Other"), other) Then
                    tt = tt.Add(other)
                Else
                    'Do something like show error message for incorrect data for dr("Other")
                End If
                Label7.Text = tt.TotalMinutes.ToString
            Else
                'Do something like show error message for incorrect data for dr("Total_Time")
            End If
        End While
    Catch ex As Exception

    End Try
End Sub

If time is more than 24:00:00, use below code

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

    con = New System.Data.SqlClient.SqlConnection
    Try
        con.ConnectionString = "Data Source=Vicky-pc\sqlexpress;Initial Catalog=customer_details;Integrated Security=True;Pooling=False"
        con.Open()

        Dim cm As SqlClient.SqlCommand
        cm = New SqlClient.SqlCommand("SELECT * FROM customer_details WHERE Id=@id", con)
        cm.Parameters.AddWithValue("@id", TextBox5.Text)
        dr = cm.ExecuteReader()
        While dr.Read()     
            Try
                Dim dataTime As String = dr("Total_Time").ToString
                dataTime = dataTime.Split("."c)(0).ToString
                Dim tt As New TimeSpan(Integer.Parse(dataTime.Split(":"c)(0)), Integer.Parse(dataTime.Split(":"c)(1)), Integer.Parse(dataTime.Split(":"c)(2)))
                dataTime = dr("Other").ToString
                dataTime = dataTime.Split("."c)(0).ToString
                Dim other As New TimeSpan(Integer.Parse(dataTime.Split(":"c)(0)), Integer.Parse(dataTime.Split(":"c)(1)), Integer.Parse(dataTime.Split(":"c)(2)))
                tt = tt.Add(other)
                dataTime = tt.TotalMinutes.ToString

            Catch ex As Exception
                'do something here as string is not a time
            End Try
        End While
    Catch ex As Exception

    End Try
End Sub

@vigneshs I think it should be `If dr.Read() Then` statement if you are reading only one record and make sure the query returns any result — Slai, Feb 19 '17 at 15:37
may be your time is more than 24 hours. edited the code to accomodate — Mukul Varshney, Feb 19 '17 at 15:42
put a break point at Dim dataTime As String = dr("Total_Time").ToString statement. Then debug step by step. dataTime should have string sent from db, then tt and other should get the value in Timespan. — Mukul Varshney, Feb 19 '17 at 16:00
is it ok to drop the milli second part of the time i.e. "18:41:14.8700000" can be "18:41:14" ? — Mukul Varshney, Feb 19 '17 at 16:08
@mukul sorry i was not feeling well i tried ur code but there is not effect and my time is not more than 24hr its just some couple of minutes — , Mar 01 '17 at 06:47

Trevor_G · Answer 3 · 2017-02-19T16:53:04.047

0

Don't keep switching to strings... and use a Timespan

If your label never changes then the routine must be erroring out, examine the error from the catch statement. However from your image it looks like Other has no value.

And you should ALWAYS test for dbnull when using databases. DBNull has some weird behaviours in Math and Boolean comparisons.

Replace your while loop with this

    If Not Dr.read OrElse IsDBNull(Dr("Total_time")) OrElse IsDBNull(Dr("Other")) Then
        Label7.text = "ERR"
    Else
        Dim ts As TimeSpan = TimeSpan.Parse(dr("Total_time").ToString)
        Label7.text = (ts.TotalMinutes * Dr("Other")).ToString
    End If

PS: Your question says multiply by Other but your form/code says Add... I went with the question.

edited Feb 19 '17 at 16:53

answered Feb 19 '17 at 15:32

Trevor_G

1,331
1
8
17

it is throwing ERR – Feb 19 '17 at 21:34
I was fallen sick and i am not able to work for sum day, but i am alright now and i have tried all the above codes and its not working – Mar 01 '17 at 06:35
it is showing err in the label7.text can u help me please – Mar 01 '17 at 06:38
If you are getting ERR, break at the IF line and see which condition it is failing on. – Trevor_G Mar 01 '17 at 09:42

How to convert total time to minutes

3 Answers3