none
Cálcular horas SQL RRS feed

  • Pergunta

  • Olá amigos, venho pedir ajuda.

    No select abaixo, estou calculando HoraFim - HoraInicio, porém, quando HoraFim = 09:30 e HoraInicio = 09:00, a consulta abaixo está retornando 30:

    Conseguem me ajudar a corrigir?

    Desde já, muito obrigado.

    SELECT ISNULL(Substring(CASE len(cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR)
    				END, 1, 2) + ':' + Substring(CASE len(cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraFinal - T1.U_RR_HoraInicio AS NVARCHAR)
    				END, 3, 4), '00:00')
    	,ISNULL(Substring(CASE len(cast(T1.U_RR_HoraInicio AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraInicio AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraInicio AS NVARCHAR)
    				END, 1, 2) + ':' + Substring(CASE len(cast(T1.U_RR_HoraInicio AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraInicio AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraInicio AS NVARCHAR)
    				END, 3, 4), '00:00') AS 'Hora Inicial'
    	,ISNULL(Substring(CASE len(cast(T1.U_RR_HoraFinal AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraFinal AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraFinal AS NVARCHAR)
    				END, 1, 2) + ':' + Substring(CASE len(cast(T1.U_RR_HoraFinal AS NVARCHAR))
    				WHEN 3
    					THEN '0' + cast(T1.U_RR_HoraFinal AS NVARCHAR)
    				ELSE cast(T1.U_RR_HoraFinal AS NVARCHAR)
    				END, 3, 4), '00:00') AS 'Hora Final'
    FROM RDR1 T1
    INNER JOIN ORDR T2 ON T2.DocEntry = T1.DocEntry
    WHERE T2.DocEntry = $[ ORDR.DocEntry]
    	AND T1.LineNum = $[ RDR1.LineNum]


    quarta-feira, 30 de maio de 2018 20:39

Respostas