How can I convert this string into days?
Example: return "2 days and 2 hours"?
SELECT '50:00:00'
How can I convert this string into days?
Example: return "2 days and 2 hours"?
SELECT '50:00:00'
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'
Divide '50' by 24 and you will have the number of days. Get the rest of the division from '50' by 24 and you'll get the rest of the hours. The minutes and seconds do not 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: link