Convert number of minutes to hh:mm

user_2902301 0
Viewed 11327

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.

blackboard time sql-server
4 Answers

Note that this is going to be like Greg's answer, but I wanted to explain and simplify it.

You have minutes, so dividing them by 60 and flooring it (removing the decimal) gives the hours (without the minutes).

If you take the total minutes again, and remove (mod it by) the floored hours - which requires conversion to minutes by multiplying by 60 - you are left with the remaining minutes by essentially just finding out what is left after taking away that many groups of sixties:

SELECT FLOOR(course_access_minutes / 60) as Hours,
(FLOOR(course_access_minutes) % 60) as Minutes
FROM MyTable

If you want the decimal to appear for the amount of minute fractions (you want the seconds to appear, in decimal form), remove FLOOR.

If you want seconds in real numbers, keep FLOOR and use what Greg had: FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60) for seconds. Be careful with the parenthesis, though, because you can end up accidentally flooring your decimaled minutes and get 0, and then 0*60 is zero:

FLOOR(
    (
        course_access_minutes - 
        FLOOR(course_access_minutes)
    ) * 60
) as Seconds
user_11815350
Related