In the query below the following error occurred:
Converting a varchar data type to a smalldatetime data type resulted in a value out of range.
By analyzing, I realized that if I take out the dbo.FN_CALC_HORAS_UTEIS
function, it executes normally, but with the included function in the query the error occurs. Below is the query and function used.
CONSULTATION
SELECT (ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,S.SMData,103) ASC)) id,
Sol.SolID,
S.SMID,
CONVERT(datetime,Sol.SolData,103),
CONVERT(datetime,Sol.SolVencimento,103),
Status = CASE ISNULL(S.SMStatus,0) WHEN 0 THEN 'Abertura Chamado' WHEN 1 THEN 'Em Atendimento' WHEN 5 THEN 'Chamado em Pausa' WHEN 9 THEN 'Chamado Concluído' END,
Responsabilidade = CASE ISNULL(S.SMMotID,0) WHEN 0 THEN 'Suporte Boavista' WHEN 4 THEN 'Aguardando Operadora' WHEN 5 THEN 'Aguardando Cliente' WHEN 6 THEN 'Aguardando Desenvolvimento' END,
CONVERT(datetime,S.SMData,103) Data,
dbo.FN_CALC_HORAS_UTEIS(CONVERT(datetime,S.SMData,103), LEAD(CONVERT(datetime,S.SMData,103)) OVER(ORDER BY CONVERT(datetime,S.SMData,103))) TempoDecorrido
FROM Solicitacao Sol
INNER JOIN
StatusMotivo S ON Sol.SolID = S.SMSolID
WHERE S.SMSolID = 55215
FUNCTION
USE [0800net_PRD]
go
/****** Object: UserDefinedFunction [dbo].[FN_CALC_HORAS_UTEIS] Script Date: 13/09/2017 10:28:29 ******/
SET ansi_nulls ON
go
SET quoted_identifier OFF
go
/*
Calcula o número de horas úteis a partir de uma data inicial, assumindo um prazo em horas.
*/
ALTER FUNCTION [dbo].[FN_CALC_HORAS_UTEIS] (@dInicial datetime,
@dFinal datetime)
returns varchar(10)
AS
BEGIN
DECLARE @dInicialTemp datetime
DECLARE @dFinalTemp datetime
DECLARE @tHoraEntrada time
DECLARE @tSaidaAlmoco time
DECLARE @tRetornoAlmoco time
DECLARE @tHoraSaida time
DECLARE @iAlmoco int = 0
DECLARE @iDiasInuteis int = 0
DECLARE @iMinutos int = 0
DECLARE @iMinutosUteisDia int = 0
DECLARE @iMinutosTotaisDia int = 0
DECLARE @Extra time
DECLARE @QtdHoras varchar(10)
SET @tHoraEntrada = '08:00'
SET @tSaidaAlmoco = '12:00'
SET @tRetornoAlmoco = '13:00'
SET @tHoraSaida = '18:00'
SET @QtdHoras = 0
SET @Extra = '01:00'
/*PRINT('DATA INICIAL: ' + CONVERT(VARCHAR, @dInicial)) */
/*PRINT('DATA FINAL: ' + CONVERT(VARCHAR, @dFinal)) */
IF CAST(@dInicial AS time) <= @tSaidaAlmoco
AND CAST(@dFinal AS time) >= @tRetornoAlmoco
BEGIN
SET @iAlmoco = DATEDIFF(minute, @tSaidaAlmoco, @tRetornoAlmoco)
/* PRINT('ALMOCO: ' + CONVERT(VARCHAR,@iAlmoco)) */
END
--WHILE DATEPART(DW, @dInicial) IN (1, 7)
WHILE (SELECT dbo.FN_DIA_NAO_TRAB(@dInicial)) = 1
AND @dInicial < @dFinal
BEGIN
SET @dInicial = CONVERT(datetime, CONVERT(varchar, @dInicial + 1, 112))
+ CONVERT(varchar, (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(hour, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(hour, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(minute, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(minute, @tHoraEntrada))))
/* PRINT ('D.INICIAL + 1º DIA UTIL: ' + CONVERT(VARCHAR, @dInicial)) */
END
WHILE (SELECT dbo.FN_DIA_NAO_TRAB(@dFinal)) = 1
AND @dInicial < @dFinal
BEGIN
SET @dFinal = CONVERT(datetime, CONVERT(varchar, @dFinal + 1, 112))
+ CONVERT(varchar, (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(hour, @tHoraSaida)))) + CONVERT(varchar, DATEPART(hour, @tHoraSaida))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(minute, @tHoraSaida)))) + CONVERT(varchar, DATEPART(minute, @tHoraSaida))))
/* PRINT ('D.FINAL + 1º DIA UTIL: ' + CONVERT(VARCHAR, @dFinal)) */
END
SET @dInicialTemp = CONVERT(datetime, CONVERT(varchar, @dInicial, 112))
+ CONVERT(varchar, (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(hour, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(hour, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(minute, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(minute, @tHoraEntrada))))
/*PRINT ('D.INICIAL TEMP: ' + CONVERT(VARCHAR, @dInicialTemp)) */
SET @dFinalTemp = CONVERT(datetime, CONVERT(varchar, @dFinal, 112))
+ CONVERT(varchar, (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(hour, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(hour, @tHoraEntrada))) + ':' + (REPLICATE('0', 2 - LEN(CONVERT(varchar, DATEPART(minute, @tHoraEntrada)))) + CONVERT(varchar, DATEPART(minute, @tHoraEntrada))))
/*PRINT ('D.FINAL TEMP: ' + CONVERT(VARCHAR, @dFinalTemp)) */
SET @iMinutosUteisDia = DATEDIFF(minute, @tHoraEntrada, @tSaidaAlmoco)
+ DATEDIFF(minute, @tRetornoAlmoco, @tHoraSaida)
/*PRINT('MINUTOS UTEIS: ' + CONVERT(VARCHAR, @iMinutosUteisDia)) */
SET @iMinutosTotaisDia = DATEDIFF(minute, @tHoraEntrada, @tHoraSaida)
/*PRINT('TOTAL MINUTOS DIA: ' + CONVERT(VARCHAR, @iMinutosTotaisDia)) */
SET @iMinutos = DATEDIFF(dd, @dInicial, @dFinal) * @iMinutosUteisDia + ((SELECT CASE
WHEN DATEDIFF(mi, @dInicialTemp, @dInicial) < 0 THEN 0
WHEN DATEDIFF(mi, @dInicialTemp, @dInicial) > @iMinutosTotaisDia THEN @iMinutosTotaisDia
ELSE DATEDIFF(mi, @dInicialTemp, @dInicial)
END) * -1 + (SELECT CASE
WHEN DATEDIFF(mi, @dFinalTemp, @dFinal) < 0 THEN 0
WHEN DATEDIFF(mi, @dFinalTemp, @dFinal) > @iMinutosTotaisDia THEN @iMinutosTotaisDia
ELSE DATEDIFF(mi, @dFinalTemp, @dFinal)
END)) + (SELECT CASE
WHEN CAST(@dInicial AS time) >= @tRetornoAlmoco
AND CAST(@dInicial AS time) <> CAST(@dFinal AS time) THEN @iMinutosTotaisDia - @iMinutosUteisDia
ELSE 0
END) - (SELECT CASE
WHEN CAST(@dFinal AS time) >= @tSaidaAlmoco
AND CAST(@dFinal AS time) <= @tRetornoAlmoco THEN @iMinutosTotaisDia - @iMinutosUteisDia + DATEDIFF(minute, @tRetornoAlmoco, @tSaidaAlmoco) - DATEDIFF(minute, CAST(@dFinal AS time), @tSaidaAlmoco)
ELSE 0
END)
/*PRINT(CONVERT(VARCHAR, ((DATEDIFF(DD, @dInicial, @dFinal) * @iMinutosUteisDia) +(@iTEMP * -1 + @iTEMP2)))) */
/*PRINT('DIAS ENTRE INI & FIM * MINUTOS UTEIS: ' + CONVERT(VARCHAR, DATEDIFF(DD, @dInicial, @dFinal) * @iMinutosUteisDia)) */
/*PRINT('MINUTOS ENTRE INI.TEMP & INI: ' + CONVERT(VARCHAR, DATEDIFF(MI, @dInicialTemp, @dInicial))) */
/*PRINT('MINUTOS ENTRE FIM.TEMP & FIM: ' + CONVERT(VARCHAR, DATEDIFF(MI, @dFinalTemp, @dFinal))) */
/*PRINT('MINUTOS: ' + CONVERT(VARCHAR, @iMinutos)) */
WHILE @dInicial < = @dFinal
BEGIN
IF (SELECT dbo.FN_DIA_NAO_TRAB(@dInicial)) = 1
BEGIN
SET @iDiasInuteis = @iDiasInuteis + 1
END
SET @dInicial = @dInicial + 1
END
/*PRINT('DIAS INUTEIS: ' + CONVERT(VARCHAR, @iDiasInuteis)) */
SET @iMinutos = (SELECT CASE
WHEN @iMinutos < (@iDiasInuteis * @iMinutosTotaisDia) THEN 0
ELSE @iMinutos - (@iDiasInuteis * @iMinutosUteisDia)
END)
/*PRINT('MINUTOS - DIAS INUTEIS: ' + CONVERT(VARCHAR, @iMinutos)) */
SET @iMinutos = @iMinutos - @iAlmoco
/*PRINT('ALMOCO: ' + CONVERT(VARCHAR, @iAlmoco)) */
/*PRINT('MINUTOS - ALMOCO: ' + CONVERT(VARCHAR, @iMinutos)) */
/*PRINT('CONVERSAO DE MINUTOS EM HORAS: ' + CONVERT(VARCHAR, CONVERT(int, @iMinutos / 60)) + ':' + RIGHT(CONVERT(VARCHAR, CONVERT(int, @iMinutos % 60) + 100), 2) ) */
SET @QtdHoras = CONVERT(varchar, CONVERT(int, @iMinutos / 60)) + ':'
+ RIGHT(CONVERT(varchar, CONVERT(int, @iMinutos % 60) + 100), 2)
RETURN @QtdHoras
END