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