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
92 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 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
transition to visual .net from vb6 5 41
where to find DTS instalation package for sql 2014 64 bit 3 12
SQL USE DATABASE VARIABLE 5 31
RAISERROR WITH NOWAIT 2 18
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 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