SQL SERVER - Convert String in Days

Question

How can I convert this string in days?

Example: return "2 days and 2 hrs"?

SELECT '50:00:00'

Answer 1

Using Ricardo’s answer and solving the case of more than 99 hours in the input string would look like this:

declare @h int, @m int, @s int, @time varchar(8), @pos int
set @time = '155:00:00'
set @pos = CHARINDEX( ':', @time )
set @h = SUBSTRING(@time, 1, @pos-1)
set @m = SUBSTRING(@time, @pos+1, 2)
set @s = SUBSTRING(@time, @pos+4, 2)

select cast (@h/24 as nvarchar(10)) + 'dias, ' + cast (@h%24 as nvarchar(10)) + 'horas, ' + cast (@m as nvarchar(2)) + 'minutos, ' + cast (@s as nvarchar(2)) + 'segundos'

Answer 2

Divide '50' by 24 and you will get the number of days.
Take the rest of the division '50' by 24 and you’ll get the rest of the hours.
Minutes and seconds don’t need to calculate.

declare @h int, @m int, @s int, @time varchar(8)
set @time = '50:00:00'
set @h = SUBSTRING(@time, 1, 2)
set @m = SUBSTRING(@time, 4, 2)
set @s = SUBSTRING(@time, 7, 2)

select cast (@h/24 as nvarchar(10)) + 'dias, ' + cast (@h%24 as nvarchar(10)) + 'horas, ' + cast (@m as nvarchar(2)) + 'minutos, ' + cast (@s as nvarchar(2)) + 'segundos'

See an example here: http://sqlfiddle.com