CODEWITHSUNDEEP
sqlsql-servert-sql
smoore4
smoore4

Reputation: 4866

Convert non-standard mm:ss to hh:mm:ss with T-SQL

I am doing some data clean up from an old, text-based system. The system tracks running time of films with a format like this: '86:08'.

This means 1 hour 26 minutes and 8 seconds.

I am able to convert the first part like this:

declare @t varchar(10)
set @t = '86:08'

select @t as t, 
t2 = try_convert(time,format(dateadd(minute,cast(left(@t, 2) as int),0),'hh:mm','en-US'))

Result:

t       t2
86:08   01:26:00.0000000

How can I get the seconds as well, so the result would be 01:26:08:0000?

Upvotes: 2

Views: 64

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You can convert the value to seconds and back to a time:

select @t as t, 
       convert(time, dateadd(second, left(@t, 2) * 60 + right(@t, 2), 0))

Upvotes: 1

CR241
CR241

Reputation: 2623

Here you go:

You need to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 86.08 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(varchar, DATEADD(ms, 86.08 * 1000, 0), 114)

Upvotes: 1

GMB
GMB

Reputation: 222722

You could recover the 2 rightmost digits and convert to seconds instead of minutes.

Consider:

try_convert(
    time,
    format(
        dateadd(second,cast(left(@t, 2) as int)*60 + cast(right(@t, 2) as int), 0),
    'hh:mm:ss','en-US')
)

Demo on DB Fiddle:

declare @t varchar(10)
set @t = '86:08'

select
    @t as t, 
   t2 = try_convert(
    time,
    format(
        dateadd(second,cast(left(@t, 2) as int)*60 + cast(right(@t, 2) as int), 0),
    'hh:mm:ss','en-US')
)

GO

t     | t2      
:---- | :-------
86:08 | 01:26:08

Upvotes: 2

Related Questions