Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
124 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 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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