SQL SERVER - Convert String in Days

5

How can I convert this string into days?

Example: return "2 days and 2 hours"?

SELECT '50:00:00'
    
asked by anonymous 20.07.2018 / 20:53

2 answers

5

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'
    
20.07.2018 / 21:45
3

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

    
20.07.2018 / 21:26