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:

  • SELECT $ (dollar sign)
  • Test unique filtered index with tSQLt
  • Getting sp value from another sp
  • SqlTransaction has completed
  • SQLServerException: The statement did not return a result set when executing SQL
  • The database is not accessible. (ObjectExplorer)
  • 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.

    3 Solutions collect form web for “Convert number of minutes to hh:mm”

    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)
    

    You could use FLOOR like this

    DECLARE @SampleData AS TABLE
    (
        Minutes numeric(18,4)
    )
    
    INSERT INTO @SampleData
    VALUES
    ( 0.0500),
    ( 1.0500),
    ( 30.0500),
    ( 80.0500),
    ( 314.0833),
    ( 625.8167)
    
    SELECT CONCAT(floor(sd.Minutes/60),':', CASE    WHEN sd.Minutes - floor(sd.Minutes/60)*60 < 1 THEN '0'
                                                    ELSE FLOOR(sd.Minutes - floor(sd.Minutes/60)*60 )
                                            END) AS hours
    FROM @SampleData sd
    

    Returns

    hours
    0:0
    0:1
    0:30
    1:20
    5:14
    10:25
    
    WITH _Samples AS (
        SELECT CONVERT(numeric(18, 4), 0.0500) [course_access_minutes]
        UNION ALL SELECT 0.0667
        UNION ALL SELECT 0.3667
        UNION ALL SELECT 314.0833
        UNION ALL SELECT 625.8167
    ) 
    SELECT 
        S.course_access_minutes, 
    
        -- split out the number
        FLOOR(S.course_access_minutes / 60) [hours], 
        FLOOR(S.course_access_minutes % 60) [minutes], 
        FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60) [seconds], 
    
        -- to a string
        CONVERT(varchar(10), FLOOR(S.course_access_minutes / 60)) 
        + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR(S.course_access_minutes % 60)), 2)
        + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60)), 2) [time_string], 
    
        -- You could consider converting to the time data type if the values will never exceed the limit
        -- time supports 00:00:00.0000000 through 23:59:59.9999999
        -- 0 through 1439.9833333 ... 23 * 60 = 1380 + 59 = 1439 + (59 / 60) = 1439.9833333 
        -- (see: https://docs.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql)
        CONVERT(time, 
            CONVERT(varchar(10), FLOOR(S.course_access_minutes / 60)) 
            + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR(S.course_access_minutes % 60)), 2)
            + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60)), 2) 
        ) [time]
    FROM 
        _Samples S 
    

    (It wouldn’t be difficult to further this idea and split out the fractional seconds as well.)

    Which yields:

    course_access_minutes  hours  minutes  seconds  time_string  time
    ---------------------- ------ -------- -------- ------------ ----------------
    0.0500                 0      0        3        0:00:03      00:00:03.0000000
    0.0667                 0      0        4        0:00:04      00:00:04.0000000
    0.3667                 0      0        22       0:00:22      00:00:22.0000000
    314.0833               5      14       4        5:14:04      05:14:04.0000000
    625.8167               10     25       49       10:25:49     10:25:49.0000000
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.