Query with datadiff returning 0 in sql server result

Navigation
0

I have the following query: 

SELECT 
DATEDIFF(hh, MIN(BATIDA), MAX(BATIDA))AS HORAS
 FROM   ARELBATIDATRANSITOVIEW
WHERE  CHAPA = 3187 
  AND BATIDA IS NOT NULL 
  AND SEQUENCIALBATIDA IN (2,3) 

  GROUP BY DATA

ORDER BY DATA DESC

Example: 

HORAS
2
2
2
1
2
2
2
3

Only it returns me the hours, I need the other fields that would be: 

SELECT 
DATA,
CHAPA,
CODCOLIGADA,
BATIDA,
STATUS,
NATUREZA,
SEQUENCIALBATIDA,
DATEDIFF(hh, MIN(BATIDA), MAX(BATIDA))AS HORAS
 FROM   ARELBATIDATRANSITOVIEW
WHERE  CHAPA = 3187 
  AND BATIDA IS NOT NULL 
  AND SEQUENCIALBATIDA IN (2,3) 

  GROUP BY DATA,CHAPA,CODCOLIGADA,BATIDA,STATUS,NATUREZA,SEQUENCIALBATIDA

ORDER BY DATA DESC

More when I sow this query with all fields the field: DATEDIFF(hh, MIN(BATIDA), MAX(BATIDA))AS HORAS returns with 0 Example: 

                                                                   HORAS
2016-10-17 00:00:00.000 123  1  2016-10-17 12:10:00.000 C   1   2   0
2016-10-17 00:00:00.000 123  1  2016-10-17 14:20:00.000 C   0   3   0
2016-10-14 00:00:00.000 123  1  2016-10-14 11:48:00.000 C   1   2   0

What could it be?

What's my interest in this hour field, listar resultados > 2

    
asked by anonymous 19.10.2016 / 03:51

1 answer

1

This should occur because DATEDIFF returns INT , and may be doing some rounding. Try to return in seconds and it will work, see the example: 

declare @d1 datetime, @d2 datetime

set @d1='2016-10-19 15:40:52.847'
set @d2='2016-10-19 15:42:52.847'

-- Aqui retorna zero
select DATEDIFF(hh, @d1, @d2) AS HORAS

-- Aqui retorna certo, 2horas
select DATEDIFF(ss, @d1, @d2)/60 AS HORAS

In your code: 

DATEDIFF(ss, MIN(BATIDA), MAX(BATIDA))/60 AS HORAS
    
19.10.2016 / 19:48
Notice: Object of class PDOStatement could not be converted to int load ajax combo when opening page