Convert Hours and Minutes into a Double.
I would like to convert hours/minutes stored as varchar into a double, in hours. 0:15 is 15 minutes; 0:30 is 30 minutes, 1:20 is 1 hour and 20 minutes, etc.
0:15 --> .25
0:30 --> .50
1:20 --> 1.33
1:30 --> 1.50
1:45 --> 1.75
Is there an easy way to do this?
0:15 --> .25
0:30 --> .50
1:20 --> 1.33
1:30 --> 1.50
1:45 --> 1.75
Is there an easy way to do this?
If vb.net:
Dim Parts() as string=YourTimeDataString.
'parts(0) will have hours
'parts(1) will have minutes
Dim Hours as Decimal=CDec(parts(0)) + CDec(parts(1))/60
Now if you want to change it in SQL itself, that is a different matter.
Dim Parts() as string=YourTimeDataString.
'parts(0) will have hours
'parts(1) will have minutes
Dim Hours as Decimal=CDec(parts(0)) + CDec(parts(1))/60
Now if you want to change it in SQL itself, that is a different matter.
ASKER
yes, I was looking to do this using SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK. Since you had it tags as vb.net/SQL Server, etc and didn't specify, there was no way I could know. It appears that Brian has shown you the same thing using TSQL. It is the same basic idea. Take the minutes part and divide by 60 to get into decimal and add that to the hours part.
ASKER
No problem. I inadvertently included vb.net tag.
ASKER
Brian - this worked, thanks.
SELECT CAST(SUBSTRING(@Time, 1, CHARINDEX(':', @Time) - 1) AS FLOAT) +
CAST(SUBSTRING(@Time, CHARINDEX(':', @Time) + 1, LEN(@Time)) AS FLOAT) / 60
Is there a easy way to round the div by 60 result to 2 decimal places?
SELECT CAST(SUBSTRING(@Time, 1, CHARINDEX(':', @Time) - 1) AS FLOAT) +
CAST(SUBSTRING(@Time, CHARINDEX(':', @Time) + 1, LEN(@Time)) AS FLOAT) / 60
Is there a easy way to round the div by 60 result to 2 decimal places?
ASKER
ROUND() works for rounding. Thanks!@
Another option is to cast the value to DECIMAL(5,2) but yes ROUND will certainly work.
https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
Mike