473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Sum of Time Difference

RN1
This is how I am calculating the time difference:

------------------------
Function TimeDiff(ByVal StartDateTime, ByVal EndDateTime)
Dim h As Integer
Dim m As Integer
Dim t1 As DateTime
Dim t2 As DateTime
Dim ts As TimeSpan

t1 = DateTime.Parse(StartDateTime)
t2 = DateTime.Parse(EndDateTime)

ts = t2 - t1
h = ts.Hours + (ts.Days * 24)
m = ts.Minutes

Return System.Math.Abs(h) & ":" & System.Math.Abs(m)
End Function
------------------------

For e.g. if StartDateTime is 05/08/2008 7:00:00 AM & EndDateTime is
05/08/2008 11:30:00 PM, then the above function will return 16:30 i.e.
16 hours & 30 minutes.

The result I am getting from the above function - I am inserting that
in a SQL Server DB table in a column named Duration whose datatype is
varchar. Now I want to add all the records under the Duration column &
get the total no. of hours & minutes.

How do I do it?
Oct 13 '08 #1
2 1949


"RN1" <rn**@rediffmail.comwrote in message
news:78**********************************@d10g2000 pra.googlegroups.com...
This is how I am calculating the time difference:

------------------------
Function TimeDiff(ByVal StartDateTime, ByVal EndDateTime)
Dim h As Integer
Dim m As Integer
Dim t1 As DateTime
Dim t2 As DateTime
Dim ts As TimeSpan

t1 = DateTime.Parse(StartDateTime)
t2 = DateTime.Parse(EndDateTime)

ts = t2 - t1
h = ts.Hours + (ts.Days * 24)
m = ts.Minutes

Return System.Math.Abs(h) & ":" & System.Math.Abs(m)
End Function
------------------------

For e.g. if StartDateTime is 05/08/2008 7:00:00 AM & EndDateTime is
05/08/2008 11:30:00 PM, then the above function will return 16:30 i.e.
16 hours & 30 minutes.

The result I am getting from the above function - I am inserting that
in a SQL Server DB table in a column named Duration whose datatype is
varchar. Now I want to add all the records under the Duration column &
get the total no. of hours & minutes.

How do I do it?
There maybe better ways, try one of the microsoft.public.sqlserver.* groups
but here's one way.
Use a combination of CHARINDEX and SUBSTRING to find the hours component,
cast to an INT and SUM. Use a similar technique for the minutes, or the
RIGHT function if there's always two digits.
Once you have these values you'll have to use the modulo (%) operator on the
minutes SUM by 60 to convert the excess to hours.
--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name

Oct 13 '08 #2
After serious thinking RN1 wrote :
This is how I am calculating the time difference:

------------------------
Function TimeDiff(ByVal StartDateTime, ByVal EndDateTime)
Dim h As Integer
Dim m As Integer
Dim t1 As DateTime
Dim t2 As DateTime
Dim ts As TimeSpan

t1 = DateTime.Parse(StartDateTime)
t2 = DateTime.Parse(EndDateTime)

ts = t2 - t1
h = ts.Hours + (ts.Days * 24)
m = ts.Minutes

Return System.Math.Abs(h) & ":" & System.Math.Abs(m)
End Function
------------------------

For e.g. if StartDateTime is 05/08/2008 7:00:00 AM & EndDateTime is
05/08/2008 11:30:00 PM, then the above function will return 16:30 i.e.
16 hours & 30 minutes.

The result I am getting from the above function - I am inserting that
in a SQL Server DB table in a column named Duration whose datatype is
varchar. Now I want to add all the records under the Duration column &
get the total no. of hours & minutes.

How do I do it?
The best way would be not to store it as a string, but as a "float".
Store the value of the TotalHours property of the TimeSpan returned by
your method. Summing that column would then be easy.

Hans Kesting
Oct 13 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jason Reljac | last post by:
Howdy, For a project I am working on right now I need to be able to calculate the time difference between to given times. (For example...The difference between 11:30 am and 1:45pm being 2:15) ...
10
by: Andreas | last post by:
Hi! Is it possible to get a time event at a specific time, for instance eight a'clock? My program is running in the background and is minimized to the tray bar. If not, is there a smooth way...
2
by: Joe User | last post by:
I am looking to calculate the difference between and event time and a sample time of Now. This is the query that I thought would do it, however I'm returning DIFFERENCE values that look the same...
6
by: cournape | last post by:
Hi there, I have some scientific application written in python. There is a good deal of list processing, but also some "simple" computation such as basic linear algebra involved. I would like to...
6
by: Michael Bulatovich | last post by:
I have a very simple db I use for keeping track of hours, tasks, projects, clients etc. It has a form that I use to enter data. Currently the form has a textbox for a field called "start time",...
7
by: Edward Mitchell | last post by:
I have a number of DateTimePicker controls, some set to dates, some set to a format of Time. The controls are all embedded in dialogs. I created the controls by dragging the DateTime picker from...
5
by: Geoff Jones | last post by:
Hi I have question regarding times and dates in a datatable. I have one table with one column having the date e.g.03/09/04, and another column other the time 08:03:05. The other table has one...
3
by: Randall Parker | last post by:
Suppose one has a database of UTC times that are from different dates in the past. The problem with translating those times to a local time is that one does not know for each UTC time whether the...
3
by: Steve | last post by:
I am trying to calculate elapsed travel times for flights. My plan is to enter the local departure time, the departure city and the local arrival time and city. These times would be standardised...
15
by: student4lifer | last post by:
Hello, I have 2 time fields dynamically generated in format "m/d/y H:m". Could someone show me a good function to calculate the time interval difference in minutes? I played with strtotime() but...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.