Convert human readable duration to Time Format SQL SERVER 2014

Question

I have this data.

"2mn 56s", "30s 83ms", "2h 10mn"

How to convert these into time format:

hh:mn:ss - "00:02:56", "00:00:30", "02:10:00"

Can you please share your efforts? – Rajesh Jan 24 '17 at 07:48 — Rajesh, Jan 24 '17 at 07:48

Pranav C Balan · Accepted Answer · 2017-01-24T08:38:15.910

1

In JavaScript, you can use String#replace method.

var str = '"2mn 56s", "30s 83ms", "2h 10mn"';

console.log(
  str.replace(/"(?:(\d)+h\s*)?(?:(\d+)mn\s*)?(?:(\d+)s\s*)?[^"]*"?/g, function(_, m1, m2, m3) {
    return [m1, m2, m3].map(v => ('00' +( v || '')).slice(-2)).join(':')
  })
)

Regex explanation here.

edited Jan 24 '17 at 08:38

answered Jan 24 '17 at 07:54

Pranav C Balan

113,687
23
165
188

@Rajesh : it may be negligible :) – Pranav C Balan Jan 24 '17 at 08:08
I was wondering `.reduce` would have suited better but I didn't know it skips `undefined` values. [JSFiddle](https://jsfiddle.net/RajeshDixit/db0xr7bd/1/). – Rajesh Jan 24 '17 at 08:23
1

@Rajesh : with `reduce`, do something like this : https://jsfiddle.net/db0xr7bd/2/ – Pranav C Balan Jan 24 '17 at 08:37

score 1 · Answer 2 · answered Jan 24 '17 at 14:23

I wouldn't store TIME in this format in the future. With that being said, based off your sample data here is a SQL Server solution. Uncomment out the @t1 variable to see different test cases.

declare @t1 varchar(16)

--set @t1 = '2h 10mn'
set @t1 = '2mn 56s'
--set @t1 = '30s 83ms'

--this places your data in a time format. This is ideal in most cases so you can actually use datetime functions on the data
select
    case 
        when left(@t1, charindex(' ',@t1)) like '%h' 
        then convert(time,convert(varchar,left(@t1,charindex('h',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2) + ':00')   
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%mn' 
        then convert(time,'00:' + convert(varchar,left(@t1,charindex('mn',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 1))
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%s'
        then convert(time,'00:00:' + convert(varchar,left(@t1,charindex('s',@t1) - 1)) + '.' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2))
    end


--if you only want it in the hh:mm:ss then you can use the below. This rounds your milliseconds properly

select
    case 
        when left(@t1, charindex(' ',@t1)) like '%h' 
        then convert(time(0),convert(varchar,left(@t1,charindex('h',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2) + ':00')    
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%mn' 
        then convert(time(0),'00:' + convert(varchar,left(@t1,charindex('mn',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 1))
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%s'
        then convert(time(0),'00:00:' + convert(varchar,left(@t1,charindex('s',@t1) - 1)) + '.' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2))
    end

Convert human readable duration to Time Format SQL SERVER 2014

2 Answers2