I have imported a large amount of data but the format of the time data is hard to work with and must be converted into a time value.
The current data is stored as a nvarchar and cannot be converted to int or time. I tried to use :
REPLACE(REPLACE(REPLACE(REPLACE ([ERGTalk time],'00d ',''),'h ',':'),'m ',':'),'s','') AS [ERGTalk Time Conversion],
as a method to strip all characters but it still can’t be converted or cast.
An example is the time value originally is 00d 00h 02m 07s and the desired result is 00:00:02:07
6
As others has mention in the comments, you can’t really convert that string to a time. it does not make sense.
You can however convert it to an integer value that represent the interval period. To do that you can’t simply convert it. You need to parse each individual element of the string. Assuming that your string is well structured and always have space to separate the element, you can use string_split() to split it and then do some simple parsing and sum() up the value.
select s.talk_time, [interval in seconds] = sum(i.i)
from #sample s
cross apply string_split(s.talk_time, ' ') v
cross apply
(
select i = left(v.value, len(v.value) - 1)
* case right(v.value, 1)
when 'd' then 24 * 60 * 60
when 'h' then 60 * 60
when 'm' then 60
when 's' then 1
end
) i
group by s.talk_time
db<>fiddle demo