Solved

How to update a sql 2012 database table with the time difference from 2 time frames in vb.net 2010

Posted on 2016-11-03
4
102 Views
Last Modified: 2016-11-04
I have a database that I need to add the difference of 2 times or (wait time). In my database table I have three fields one each for start time, end time, and total wait time. When the table was first created the start and end time got populated but not the wait time. Here is the part of code that I tried to populate the wait time field with.

Dim diff As TimeSpan = CDate(Now.ToShortTimeString).Subtract(CDate(txtInTime.Text))
                TextBox9.Text = (diff.TotalMinutes)
                TextTotalWaitTime.Text = TextBox9.Text.ToString
                txtTotalTime.Text = TextBox9.Text

further in the code is where I update the database with the total wait time.

What I need to be able to do is update all the existing records with the time difference in minutes from Start Time and End Time fields and populate those in the Total Wait Time field in minutes. Going forward I would need to modify my code with the right code to start populating the Total Wait Time field when the program runs.
0
Comment
Question by:samiam41
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41872611
Why not have SQL Server update the table?

UPDATE dbo.myTable
SET TotalWaitTime = DATEDIFF(MINUTE,StartTime,EndTime)

Open in new window

1
 
LVL 40

Expert Comment

by:lcohan
ID: 41872782
You could have that column defined as a "Computed Columns" in SQL so you don't need to change any code but alter you back-end table as described here:

https://msdn.microsoft.com/en-us/library/ms188300.aspx
1
 
LVL 9

Author Closing Comment

by:samiam41
ID: 41874518
Thanks this was a huge help. Now my program will do the rest.
0
 
LVL 9

Author Comment

by:samiam41
ID: 41874521
Icohan, I also liked your solution but not for this process. I would have given you at least 100 points if I could have split the points like that. I know I can find a use for Computed Columns in another SQL project.

Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question