Sum of Time Difference

  • Thread starter Thread starter RN1
  • Start date Start date
R

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?
 
RN1 said:
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?
Click to expand...
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.
 
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?
Click to expand...

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
 
Back
Top