Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
135 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
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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