I have a column in a table that stores the number of minutes as a numeric(18,4)
field named [course_access_minutes]
.
The stored values come from a blackboard database and look like this:
0.0500
0.0667
0.3667
up to
314.0833
625.8167
How do I convert these to time hh:mm, I've had a good look at the database documentation and all I can find is
course_access_minutes numeric(18,4) This is the number of minutes that the user accesses this course in total during this login session.
Can I assume that I can make a direct conversion from minutes into hours? I think I will take any values below 1 as 0 minutes. What is the best way to do this in SQL? Thanks in advance for your help.
Try this
SELECT CONVERT(varchar, DATEADD(s, 625.8167 * 60, 0), 108)
If the duration is longer than 24 hours you can use this
SELECT CONVERT(varchar, CAST(1877.4501 * 60 AS int) / 3600)
+ RIGHT(CONVERT(varchar, DATEADD(s, 1877.4501 * 60, 0), 108), 6)