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

Asked

Viewed 4,225 times

1

I am developing an SQL query and in case I need to convert a field of hours that is in varchar for time to 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 fails to convert.

What could I do in this case? In this case the Query I am using is the following 

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

Remembering that the field [TIME UNAVAILABLE] is Varchar.

sql sql-server

  • I believe you’re mixing concepts. time in SQL Server refers to a specific point of date and time, not the elapsed time interval. I believe it is possible to add a time a numeric, where the numeric would serve as the amount of hours in an interval and the sum is interpreted as "what will be the time after a time interval from my base time"

2 answers

1

Like your field tempo_indisponivel refers to time lengths which may contain values greater than 24h, it is not possible to convert it to the type Time.

The field of the type Team in Sqlserver refers to the hour of a day based on a 24h clock. Allowed values should be in the range between 00:00:00.0000000 and 23:59:59.9999999.

My suggestion would be to work with the time unit in seconds (using int). To convert to seconds, whereas the field varchar store time in format horas:minutos:segundos, 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" on Sqlserver would be to use the type Datetime having the standard value 01/01/1900 00:00:00 as the initial date (D0).

Durations would be represented as a shift 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 time duration varchar for DateTime, could be used the following query:

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

0

Hello,

Here’s an example I use here at 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

Upshot

|horas | minutos|
| 27   |   30   |

I hope I’ve helped

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.