Calculate Time

Hello,

is there any way to calculate time by using SQL statement?
for example:

i am working to calculate how long (duration) between two time(in the same day), let say overtime hours.
The fields in my database are...

timein :- 14:00
timeout :- 18:00
totaltime : ?

i am expecting the result to be in minute(s).

please help me.


regards usin.

VB offer a nice object called Date and you can use
DateDiff and such methods that will help you calculate the difference in minute. You might give a look to that.

Nic

Here is an example...
Me.Caption = DateDiff("n", Format(Text1.Text, "HH:MM"), Format(Text2.Text, "HH:MM"))

But if start time is past the end time or past midnight it returns a negative number.

If you want something more complicated, here it is...It returns hours formated like this 14:00
Private Sub Command1_Click()
Me.Caption = fnCalcHours(Text1.Text, Text2.Text)'send with this format 14:00
End Sub


Public Function fnCalcHours(dTimeFrom As String, dTimeTo As String) As String
On Error Resume Next
Dim CalcHours As Integer, xf As Integer, xt As Integer, xRight As Integer, strMin As String

dTimeFrom = Replace(dTimeFrom, ".", ":")
dTimeTo = Replace(dTimeTo, ".", ":")


If Not IsNumeric(Right$(dTimeFrom, 2)) Or Not IsNumeric(Right$(dTimeTo, 2)) Then
fnCalcHours = "Error"
Exit Function
End If

If Not IsNumeric(Left$(dTimeFrom, 2)) Then
dTimeFrom = "0" & dTimeFrom
End If

If Not IsNumeric(Left$(dTimeTo, 2)) Then
dTimeTo = "0" & dTimeTo
End If

If Left$(dTimeFrom, 2) = 24 Then
dTimeFrom = "00:" & Right$(dTimeFrom, 2)
ElseIf Left$(dTimeTo, 2) = 24 Then
dTimeTo = "00:" & Right$(dTimeTo, 2)
End If

If Left$(dTimeFrom, 1) > 2 Or Left$(dTimeTo, 1) > 2 Then
fnCalcHours = "Error"
Exit Function
End If

If Left$(dTimeFrom, 2) > 24 Or Left$(dTimeTo, 2) > 24 Then
fnCalcHours = "Error"
Exit Function
End If

CalcHours = DateDiff("h", dTimeFrom, dTimeTo)
xf = Right$(dTimeFrom, 2)
xt = Right$(dTimeTo, 2)

xRight = xt - xf

If xRight < 0 Then
xRight = xRight + 60
CalcHours = CalcHours - 1
End If

If xRight >= 0 And xRight < 10 Then
strMin = "0" & xRight
Else
strMin = xRight
End If

If CalcHours < 0 Then
fnCalcHours = CalcHours + 24 & ":" & strMin
Else
fnCalcHours = CalcHours & ":" & strMin
End If

End Function

Try this:

SELECT timein, timeout, DATEDIFF(hours,timein,timeout) AS TOTALTIME
FROM table

... will give you the result in hours, you can also have it in days, minutes, seconds if you want.

... note in SQL you all have many date functions like DATEPART, DATENAME, GETDATE, DATEADD

RF

thank you guys,

it really helps

best regards
usin