Difference between hours without taking into account the date

Navigation
0

Good evening,

I have the following code: 

WITH
CONSULTA AS (
 SELECT 
    V.CHAPA,
    V.DATA,
    MAX(V.BATIDA) AS MAXBATIDA,
    MIN(V.BATIDA) AS MINBATIDA
      FROM
            ARELBATIDATRANSITOVIEW  AS V                            
        WHERE
          V.CODCOLIGADA = 1
           AND  BATIDA IS NOT NULL 
           AND  YEAR ( V.DATA ) = 2016 
             GROUP BY V.CHAPA,
                       V.DATA
),
CONSULTASEQ AS (
SELECT *, SEQ= ROW_NUMBER() OVERPARTITION BY CHAPA ORDER BY DATA)
  FROM CONSULTA
)
SELECT 
        C1.CHAPA,  
        C1.MAXBATIDA,
        C2.MINBATIDA, 
        DATEDIFF(HOUR, C1.MAXBATIDA, C2.MINBATIDA) AS HORAS,
        'TESTE DE HORAS' AS DESCRICAO
  FROM CONSULTASEQAS C1
       LEFT JOIN CONSULTASEQ AS C2 ON C1.CHAPA = C2.CHAPA AND C1.SEQ = C2.SEQ -1
             WHERE DATEDIFF(HOUR,  C1.MAXBATIDA, C2.MINBATIDA) < 11 
                         ORDER BY C1.MAXBATIDA ASC

It returns me: 

8636;2016-06-04 22:31:00.000;2016-06-06 12:59:00.000;38;TESTE DE HORAS
9042;2016-06-04 22:31:00.000;2016-06-06 12:28:00.000;38;TESTE DE HORAS
9035;2016-06-04 22:32:00.000;2016-06-06 13:31:00.000;39;TESTE DE HORAS

In the hour field he is calculating the difference between the maximum of one hour and the minimum of another, more precise than in fields like this: 

8636;2016-06-04 22:31:00.000;2016-06-06 12:59:00.000;38;TESTE DE HORAS
9042;2016-06-04 22:31:00.000;2016-06-06 12:28:00.000;38;TESTE DE HORAS

I need to return the time difference without considering the date, for example: 

 DIFF ENTRE 22:31:00  E  12:59:00 SEJA:

22:31
23:31
00:31
01:31
02:31
03:31
04:31
05:31
06:31
07:31
08:31
09:31
10:31
11:31
12:31
12:56

That is, without comparing the dates, would that be possible?

SQL SERVER 2008

    
asked by anonymous 25.10.2016 / 00:30

1 answer

2

A very simple way is this: 

select CAST((C1.MAXBATIDA-C2.MINBATIDA) as time(0)) Horas

See this example: 

declare @d1 datetime, @d2 datetime
set @d1='20161024 21:18:41.330'
set @d2='20161024 23:28:41.330'
select CAST((@d2-@d1) as time(0)) Horas
    
25.10.2016 / 01:26
How to delete all records from the database Div Fill in the blank [duplicate]