Search code examples
sql-servertimeblackboard

Convert number of minutes to hh:mm

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.

Solution

  • 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)