none
Converting miliseconds to minutes:seconds

    Question

  • I have the column duration(nvarchar datatype) with in a table.

    how to convert milliseconds to minutes:seconds?

    duration   Expected output

    3000           0.03

    2000           0.02

    19000         0.19

    24000         0.24

    35000         0.35

    37000         0.37

    70000        1.01

    • Moved by Darren GosbellMVP Thursday, January 10, 2019 11:46 PM This is a T-SQL question
    Wednesday, January 9, 2019 6:47 PM

All replies

  • Hi Priviya,

    You could use query like below to achieve this goal

    select * , floor(a / (1000 * 60)) as mins  , floor(a / (1000 )) % 60   as ses ,convert(varchar(20),floor(a / (1000 * 60))) +':'+ convert(varchar(20), floor(a / (1000 )) % 60)   as time  from  (select 3000  as a
    union all
    select 10000  as a
    union all
    select 3000  as a
    union all
    select 2000  as a
    union all
    select 70000  as a
    union all
    select 37000  as a
    )t
    

    You could refer to this post for details.

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 10, 2019 3:09 AM
  • Hi,

    Try below approach.

    Milliseconds are 1/1000th of a second.  So:

    Minutes = Floor([ms]/1000/60)

    Seconds = Mod([ms],1000*60)/1000

    ------------------------

    declare @milli as int
    set @milli = 11202079

    select @milli/3600000 as hours,
    (@milli - ((@milli/3600000)*3600000))/60000 as minutes,
    (@milli - (((@milli)/60000)*60000)) /1000 as seconds,
    @milli-(((@milli)/1000)*1000) as milliseconds 



    Friday, January 11, 2019 9:09 AM
  • DECLARE	@Sample TABLE
    	(
    		Duration NVARCHAR(100) NOT NULL,
    		Expected NVARCHAR(100) NOT NULL
    	);
    
    INSERT	@Sample
    VALUES	('3000', '0.03'),
    	('2000', '0.02'),
    	('19000', '0.19'),
    	('24000', '0.24'),
    	('35000', '0.35'),
    	('37000', '0.37'),
    	('70000', '1.01');
    
    -- swePeso
    SELECT	*,
    	CONVERT(CHAR(8), DATEADD(MILLISECOND, CAST(Duration AS BIGINT), 0), 8)
    FROM	@Sample;


    N 56°04'39.26"
    E 12°55'05.63"

    Friday, January 11, 2019 10:36 AM
  • create table test(duration nvarchar(10))

    insert into test values('3000')
    insert into test values('2000')
    insert into test values('19000')
    insert into test values('24000')
    insert into test values('35000')
    insert into test values('37000')
    insert into test values('70000')

    select duration, cast((duration/1000/60) as varchar(5)) + '.' + right('0' + cast(((duration/1000)%60) as varchar(5)), 2) from test
    Friday, January 11, 2019 11:01 AM
  • Caveat: Using TIME for durations can easily lead to overflows..

    DECLARE @Sample TABLE (
        DurationText NVARCHAR(100) NOT NULL ,
        ExpectedText NVARCHAR(100) NOT NULL
    );
    
    INSERT @Sample
    VALUES ( '3000', '0.03' ) ,
           ( '2000', '0.02' ) ,
           ( '19000', '0.19' ) ,
           ( '24000', '0.24' ) ,
           ( '35000', '0.35' ) ,
           ( '37000', '0.37' ) ,
           ( '70000', '1.01' ) ,
           ( '86401000', '1440:01' );
    
    WITH Normalized
    AS ( SELECT * ,
                CAST(S.DurationText AS BIGINT) AS DurationInMillisecondsNumber
         FROM   @Sample S )
    SELECT * ,
           CONVERT(CHAR(8), DATEADD(MILLISECOND, N.DurationInMillisecondsNumber, 0), 8) AS swePeso ,
           N.DurationInMillisecondsNumber / 60000 AS DurationMinutesNumber ,
           N.DurationInMillisecondsNumber % 60000 / 1000 AS DurationSecondsNumber ,
           CAST(N.DurationInMillisecondsNumber / 60000 AS NVARCHAR(255)) + ':' + FORMAT(N.DurationInMillisecondsNumber % 60000 / 1000, '00') AS FormattedDurationText
    FROM   Normalized N;

    Friday, January 11, 2019 1:03 PM
  • Select *,Format(dateadd(ms,try_cast(duration as int),'1900-01-01'),'mm:ss') [minutes:seconds]
    from test

    Friday, January 11, 2019 2:36 PM
    Moderator