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?

[Joe Fawcett]

Re: Sum of Time Difference



"RN1" <rn5a@rediffmai l.comwrote in message
news:786e8dce-3f53-455c-8002-6f2de6f68a3c@d1 0g2000pra.googl egroups.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.publi c.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

[Hans Kesting]

Re: Sum of Time Difference

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