Error converting from varchar to smalldatetime

Asked

Viewed 1,018 times

3

In the query below the following error occurred:

Conversion of a data type varchar into a data type smalldatetime resulted in a value outside the range.

Looking at it, I realized that if I take the function dbo.FN_CALC_HORAS_UTEIS, it performs normally, however, with the function included in the query the error occurs. Below is the query and the 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
database sql-server query function sql-server-2012

  • The function that placed the code was the FN_DIA_NAO_TRAB and not the FN_CALC_HORAS_UTEIS

  • True Alexandre Cavaloti, my mistake, I put there the function FN_CALC_HORAS_UTEIS.

  • @Caiqueromero, I tried to use Convert in several ways, however, as I said, it is in the function that this the problem, because when removing it from the query, normally executes.

  • My comment is no longer valid after editing

  • It may be related to: https://support.solarwinds.com/Success_Center/Mobile_Admin/SQL_error_The_conversion_of_a_varchar_data_type_to_a_datetime_data_type_resulted_in_an_out_of_range_value

  • Question: Any reason not to save in smalldatetime directly at the bank? Save dates as varchar is well gambiarra.

Show 1 more comment

1 answer

0


I suggest you simplify your function:

ALTER FUNCTION dbo.fn_calc_horas_uteis(@inicio datetime,
                                       @fim    datetime)
RETURNS NUMERIC(15, 2)
AS
BEGIN
  DECLARE @minutos INT;

  DECLARE @horarios TABLE(entrada TIME,
                          saida   TIME);

  INSERT INTO @horarios(entrada, saida)
                 values('08:00', '12:00'),
                       ('13:00', '18:00');

  WITH dias AS (
    SELECT CAST(@inicio AS DATE) AS dia
     UNION ALL
    SELECT DATEADD(DAY, 1, d.dia)
      FROM dias d
     WHERE d.dia < @fim
  )
  SELECT @minutos = SUM(DATEDIFF(MINUTE, h.entrada, h.saida))
    FROM dias d
         CROSS JOIN @horarios h
   WHERE dbo.fn_dia_nao_trab(d.dia) <> 1
  OPTION(MAXRECURSION 0);

  RETURN @minutos / 60;
END;

I checked here your other questions and noticed that the function fn_dia_nao_trab gets a SMALLDATETIME as parameter. Change to DATE that the purpose of the function will be reached and the error will no longer occur.

  • my great, the d.dia cannot be identified

  • @Renanbessa corrected

Browser other questions tagged database sql-server query function sql-server-2012

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