How to calculate time difference stored in SQL database using VB.NET

Question

0.00/5 (No votes)

See more:

Hi, I have a column name (Time_IN, Time_OUT, Total) in sql database.
In time_IN, the stored value is String '07:00:00 AM" and in Time_OUT=Current Time or lblTime.text

I want to Add the two columns and insert into column total using vb.net.

Thanks in advance.

This is my code but I dont know how to select TimeIN from table TimeLog

VB

Dim dFrom As DateTime  
                Dim dTo As DateTime  
                Dim sDateFrom As String = 'I want to select value from time_IN
                Dim sDateTo As String = lblTime.Text 'Current Time  
                If DateTime.TryParse(sDateFrom, dFrom) AndAlso DateTime.TryParse(sDateTo, dTo) Then  
                    Dim TS As TimeSpan = dTo - dFrom  
                    Dim hour As Integer = TS.Hours  
                    Dim mins As Integer = TS.Minutes  
                    Dim secs As Integer = TS.Seconds  
                    Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")  
                    txttotal.Text = timeDiff  
                End If

What I have tried:

VB

Dim dFrom As DateTime  
                Dim dTo As DateTime  
                Dim sDateFrom As String = lbltime.text 'current time
                Dim sDateTo As String = lblTime.Text 'Current Time  
                If DateTime.TryParse(sDateFrom, dFrom) AndAlso DateTime.TryParse(sDateTo, dTo) Then  
                    Dim TS As TimeSpan = dTo - dFrom  
                    Dim hour As Integer = TS.Hours  
                    Dim mins As Integer = TS.Minutes  
                    Dim secs As Integer = TS.Seconds  
                    Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")  
                    txttotal.Text = timeDiff  
                End If

'no error but the result is 0:00:00

Posted 31-May-17 21:54pm

Member 13234654

Updated 7-Jun-17 3:20am

Add a Solution

Comments

Richard MacCutchan 1-Jun-17 8:00am

Why are you storing times as strings, when SQL has specific types for dates and times?

Richard Deeming 1-Jun-17 9:11am

Date and Time types[^]

Sounds like you want the time[^] type.

Maciej Los 1-Jun-17 15:49pm

Please, provide sample data...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13223909 · Answer 1 · 2017-06-07T03:20:00

You should use the correct data types for storing the data in sql but this will get you going for now

just change the strConnectionString for your own.

VB

Dim sDateFrom As String = Nothing
   Dim dFrom As DateTime
   Dim dTo As DateTime
   Dim sDateTo As String = lblTime.Text 'Current Time
   Dim connection As New SqlConnection(strConnectionString)
   Using connection
       Dim command As SqlCommand = New SqlCommand("select top 1 TimeIN from TimeLog;", connection)
       connection.Open()
       Dim reader As SqlDataReader = command.ExecuteReader()
       If reader.HasRows Then
           Do While reader.Read()
               sDateFrom = reader.GetValue(0) ' TimeIn value
           Loop
       End If
   End Using

   sDateFrom = DateTime.TryParse(sDateFrom, dFrom)
   sDateTo = DateTime.TryParse(sDateTo, dTo)
   Dim TS As TimeSpan = dFrom - dTo
   Dim hour As Integer = TS.Hours
   Dim mins As Integer = TS.Minutes
   Dim secs As Integer = TS.Seconds
   Dim timeDiff As String = ((hour.ToString("00") & ":") + mins.ToString("00") & ":") + secs.ToString("00")
   txttotal.Text = timeDiff