Sql Server - Time Conversion (HH:mm) to Decimal

Question

Hello, I am working on a system that requires conversion of hours to decimal and is showing error conversion from HOUR (HH:mm) to decimal when total hours exceeds 23:59.

For better understanding if I try to convert to decimal the TIME '23:59' does not occur error, however if I try to convert '60:59' occurs the error 'The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

What would be the ideal way for a total of hours:minutes greater than or equal to 24:00 to be converted correctly to decimal?

DECLARE @HorasAtendidas VARCHAR(5) = '23:59', --Se mudar para 60:59 ocorre o erro citado.
@CargaHoraria DECIMAL(18, 2)


SET @CargaHoraria = (
SELECT
                CONVERT(NUMERIC(18,4),ROUND(DATEDIFF(ms, 0, 
                    RIGHT('0' + CONVERT(VARCHAR, SUM(CONVERT(INT, LEFT(@HorasAtendidas, 2))) + (((SUM(CONVERT (INT, RIGHT (@HorasAtendidas, 2)))) - (SUM(CONVERT(INT, RIGHT(@HorasAtendidas, 2))) % 60)) / 60)), 2) + ':' +  
                        RIGHT('0' + CONVERT(VARCHAR, SUM(CONVERT(INT, RIGHT(@HorasAtendidas, 2))) % 60), 2)     
                ) / 3600000.000000, 4))
                )

select @CargaHoraria

Thanks in advance.

Answer 1

Richard, follow a suggestion for testing, make sure the expected results are obtained:

DECLARE 
  @HorasAtendidas VARCHAR(5) = '60:59',
  @HorasAtendidasInt int,
  @CargaHoraria DECIMAL(18, 2);

SET @HorasAtendidasInt = CONVERT(INT, REPLACE(@HorasAtendidas, ':', ''));
SET @CargaHoraria = @HorasAtendidasInt / 100 + @HorasAtendidasInt % 100 / 60.0;

select @CargaHoraria

I hope it helps