How do I convert a decimal value into MM:SS on SQL?

Question

I am using the Dataiku DSS platform for data recipes and in one of my datasets, there are four columns named AvgACWTime_avg, AvgSpeedAns_avg, AvgACDTime_avg and AvgAbanTime_avg for which there are numerical time values in seconds, simply stored as decimals. I would like to convert these into a HH:MM format, what code do I need for this? Assume that I would like any new columns to be called ACWmin, Speedmin, ACDmin and Abanmin.

I have tried using the following formulas:

WITH ACWmin AS 
(
    SELECT *
    FROM (VALUES (AvgACWTime_avg)) AS t(AvgACWTime_avg)
)
SELECT 
    CAST(AvgACWTime_avg AS decimal(12, 2)) / 60 
FROM 
    ACWmin;
FROM "database"."dataset"

and

DECLARE ACWmin datetime

SELECT ACWmin = DATEADD(mi, AvgACWTime_avg * 60, '00:00') 
FROM "database"."dataset"

Neither of them have worked for me. I don't understand whether I am missing something with syntax or there is something else that I'm not doing correctly.

score 0 · Answer 1 · answered Aug 11 '23 at 06:00

For mm:ss do this:

to_string(trunc([DecimalValue])) + ":" +
to_string(trunc(60 * ([DecimalValue] - trunc([DecimalValue]))))

e.g:

SELECT
    to_string(trunc(AvgACWTime_avg)) + ":" +
    to_string(trunc(60 * (AvgACWTime_avg - trunc(AvgACWTime_avg)))) AS ACWmin
    
  , to_string(trunc(AvgSpeedAns_avg)) + ":" +
    to_string(trunc(60 * (AvgSpeedAns_avg - trunc(AvgSpeedAns_avg)))) AS Speedmin
    
  , to_string(trunc(AvgACDTime_avg)) + ":" +
    to_string(trunc(60 * (AvgACDTime_avg - trunc(AvgACDTime_avg)))) AS ACDmin
    
  , to_string(trunc(AvgAbanTime_avg)) + ":" +
    to_string(trunc(60 * (AvgAbanTime_avg - trunc(AvgAbanTime_avg)))) AS Abanmin
FROM
    YourTable;

For formatting decimal into hh:mm:ss use this:

to_string(trunc([DecimalValue])) + ":" +
to_string(trunc(60 * ([DecimalValue] - trunc([DecimalValue])))) + ":" +
to_string(trunc(60 * (60 * ([DecimalValue] - trunc([DecimalValue])) - trunc(60 * ([DecimalValue] - trunc([DecimalValue]))))))

You might want to use a function to make this useful elsewhere:

CREATE FUNCTION DecimalToMMSS (@decimalValue DECIMAL(10, 2))
RETURNS NVARCHAR(5)
AS
BEGIN
    DECLARE @minutes INT = CAST(@decimalValue AS INT);
    DECLARE @seconds INT = CAST((@decimalValue - @minutes) * 60 AS INT);
    RETURN FORMAT(@minutes, '00') + ':' + FORMAT(@seconds, '00');
END;

or:

CREATE FUNCTION DecimalToHHMMSS (@decimalValue DECIMAL(10, 2))
RETURNS NVARCHAR(8)
AS
BEGIN
    DECLARE @hours INT = CAST(@decimalValue AS INT);
    DECLARE @minutes INT = CAST((@decimalValue - @hours) * 60 AS INT);
    DECLARE @seconds INT = CAST(((@decimalValue - @hours) * 60 - @minutes) * 60 AS INT);
    RETURN FORMAT(@hours, '00') + ':' + FORMAT(@minutes, '00') + ':' + FORMAT(@seconds, '00');
END;

How do I convert a decimal value into MM:SS on SQL?

1 Answers1