Function for converting to minutes in decimal?

[Masahiro Ito]

I have column of data that have elapsed time in minutes.
For example:
1:15
1:47
23:12

I like to be able to use sql server to do simple math functions. Does
anyone have a simple function/formula that will convert into a decimal
minutes form?

EG: 1:30 should equal 1.5 minutes

Thanking you.

Masa

[Jody Gelowitz]

Once you have the Hours (whole number part of the value), Minutes, and
Seconds (if you are using Seconds as well), the formula is:

Value = Hours +( (Minutes + Seconds/60)/60)

If you are not using Seconds, then you would use:

Value = Hours + (Minutes/60)

Jody
"Masahiro Ito" <ma**@pleasespa mgoaway.it> wrote in message
news:Xn******** *************** *@216.196.105.1 30...

I have column of data that have elapsed time in minutes.
For example:
1:15
1:47
23:12

I like to be able to use sql server to do simple math functions. Does
anyone have a simple function/formula that will convert into a decimal
minutes form?

EG: 1:30 should equal 1.5 minutes

Thanking you.

Masa

[Masahiro Ito]

"Jody Gelowitz" <jo**@mail.visu alstatement.com > wrote in news:

Once you have the Hours (whole number part of the value), Minutes, and
Seconds (if you are using Seconds as well), the formula is:
Value = Hours +( (Minutes + Seconds/60)/60)
If you are not using Seconds, then you would use:
Value = Hours + (Minutes/60)

Thank you Jody.

I am trying this formula, but have trouble with it.
my numbers come from a text file like: 1:15, 1:45, 10:37, 123:14. They
are always minutes, then seconds.

My code looks much more complicated than your formula, but I can't seem
to make your formula work?

Here is my code. I think I got it working, but it runs quite slow - I do
this to millions of numbers.
Public Function Time2Decimal(By Val s As String)
Dim i As Integer
Dim dbl As Double
Dim str() As String = s.Split(":")
If str(1) = "00" Then
dbl = CType(str(0), Double)
Else
Dim dbl2 As Double = CType(str(1), Double)
dbl2 = dbl2 / 60
str(1) = dbl2.ToString
Dim split() As String = str(1).Split(". ")
If split(1).Length > 2 Then
split(1) = split(1).Remove (2, split(1).Length - 2)
End If
Dim sFin As String = str(0) & "." & split(1)
dbl = CType(sFin, Double)
End If
Return dbl
End Function

Masa

[Tom Leylan]

"Masahiro Ito" <ma**@pleasespa mgoaway.it> wrote...

My code looks much more complicated than your formula, but I can't seem
to make your formula work?

This should work:

Private Function TimeToMinutes(B yVal s As String) As Double
Dim strs() As String = s.Split(":"c)
MinSeconds = CDbl(strs(0)) + (CDbl(strs(1)) / 60)
End Function

Test it with this:

Console.WriteLi ne(TimeToMinute s("1:15").ToStr ing)
Console.WriteLi ne(TimeToMinute s("2:45").ToStr ing)
Console.WriteLi ne(TimeToMinute s("30:37").ToSt ring)
Console.WriteLi ne(TimeToMinute s("123:14").ToS tring)

By the way... if it is possible that the string could be "0" or even "15"
with no ":" separator it is a good idea to avoid adding additional code in
the algorithm to handle it. Rather conform the data right away and use the
common routine. So for instance if you do need to check for zero or a
single value (you have to decide if a single value represents minutes or
seconds) then check for the separator and if there isn't one then just add
it. Pass this along to the converter. If you need to you can place those
lines of code in TimeToMinutes but they should appear first... not
interspersed inside.

Tom

[Tom Leylan]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote...

Oh wouldn't you know... I changed the function name as I posted the code and
missed the return value...

Private Function TimeToMinutes(B yVal s As String) As Double
Dim strs() As String = s.Split(":"c)
MinSeconds = CDbl(strs(0)) + (CDbl(strs(1)) / 60)
End Function

Here it is again with the lines that will handle "0" or "15" (assuming any
value without the ":" separator is seconds. It is easy to make it assume
minutes if you need that instead.

Private Function TimeToMinutes(B yVal s As String) As Double

If s.IndexOf(":"c) < 0 Then
s = "0:" & s
End If

Dim strs() As String = s.Split(":"c)
Return Convert.ToDoubl e(strs(0)) + (Convert.ToDoub le(strs(1)) / 60)

End Function
Console.WriteLi ne(TimeToMinute s("0").ToString )
Console.WriteLi ne(TimeToMinute s("15").ToStrin g)

Console.WriteLi ne(TimeToMinute s("1:15").ToStr ing)
Console.WriteLi ne(TimeToMinute s("2:45").ToStr ing)
Console.WriteLi ne(TimeToMinute s("30:37").ToSt ring)
Console.WriteLi ne(TimeToMinute s("401:20").ToS tring)

[Masahiro Ito]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in

Here it is again with the lines that will handle "0" or "15" (assuming
any value without the ":" separator is seconds. It is easy to make it
assume minutes if you need that instead.
Private Function TimeToMinutes(B yVal s As String) As Double
If s.IndexOf(":"c) < 0 Then
s = "0:" & s
End If
Dim strs() As String = s.Split(":"c)
Return Convert.ToDoubl e(strs(0)) + (Convert.ToDoub le(strs(1))
/ 60)

Very very thank you! perfect code - much better than mine.