Convert Varchar to Time in SQL with data longer than 24 hours

Question

Convert Varchar to Time in SQL with data longer than 24 hours

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I am developing a query in SQL and in case I need to convert a field of hours that is in varchar to time and then make a media of this field (I will use Datediff together). The problem is that CONVERT only does the conversion to data until 23:59:59, if it is above that it can not convert.

What could you do in this case? In case the Query I'm using is the following

SELECT CONVERT(time,[TEMPO INDISPONÍVEL], 114) as tempo_indisponivel 
FROM [Ocorrencias].[dbo].[ind_operadora]

Remembering that the [INDISPONIBLE TIME] field is Varchar.

sql sql-server

asked by anonymous 05.01.2018 / 03:14

2 answers

HTML component word processor Error after View being called

score 1 · Answer 1

Since your tempo_indisponivel field refers to durations of time that may contain values greater than 24h, you can not convert it to type Time .

The field of type Time in SqlServer refers to the time of day based on a 24-hour clock. Allowed values should be in the range of 00:00:00.0000000 to 23:59:59.9999999 .

My suggestion would be to work with the unit of time in seconds (using int ). To convert to seconds, assuming the varchar field stores the time in horas:minutos:segundos format, just do:

declare @tempo varchar(9) = '72:05:38'

select cast( left( @tempo,charindex(':', @tempo) - 1) as int) * 3600
       +cast( substring( @tempo,charindex(':', @tempo) + 1 ,2) as int) * 60
       +cast( right( @tempo,2) as int) as tempo_em_segundos

--Resultado: 259538

To convert back the duration time in seconds to type varchar , just do:

declare @tempo_em_segundos int = 259538

select cast(@tempo_em_segundos/3600 as varchar(3))
      +':'+right('0'+cast(@tempo_em_segundos%3600/60 as varchar(2)) ,2)
      +':'+right('0'+cast(@tempo_em_segundos%60 as varchar(2)) ,2)

--Resultado: '72:05:38'

Another solution

Another way to work with "time durations" in SqlServer would be to use the DateTime having the default value 01/01/1900 00:00:00 as the start date (D0).

The durations would be represented as a displacement from D0. For example:

Duração     Representação em DateTime     Significado
12:30:01    01/01/1900 12:30:01           0 Dia 12h 30min 01seg
25:15:30    02/01/1900 01:15:30           1 Dia 01h 15min 30seg

To convert a duration of time varchar to DateTime , the following query could be used:

declare @tempo varchar(9) = '72:05:38'

select dateadd
       (day
        ,cast( left( @tempo,charindex(':', @tempo) - 1) as int) / 24
        ,cast( cast( cast( left( @tempo,charindex(':', @tempo) - 1 ) 
                           as int) % 24 
                     as varchar(3)) + substring( @tempo,charindex(':', @tempo) ,6) 
               as datetime) )

--Resultado: 04/01/1900 00:05:38

score 0 · Answer 2

Hello,

Here is an example that I use here in the company and found in some forum on the net, but if you need to use seconds you will have to make a small modification.

declare @tempo table (hora varchar(9))

insert into @tempo values ('23:59')
insert into @tempo values ('03:31')


select minutos_total / 60 as horas, minutos_total % 60 as minutos
from (
select sum(
 cast(left(hora,charindex(':',hora) - 1) as int) * 60 +
 cast(right(hora,len(hora)-charindex(':',hora)) as int)) as minutos_total
from @tempo) as resultado

Result

|horas | minutos|
| 27   |   30   |

I hope I have helped