SQL - query of minutes elapsed

Question

SQL - query of minutes elapsed

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)
#4 by (0 votes)

2

I have the following query:

SELECT BELNR_ID
    ,BELPOS_ID
    ,DocDate
    ,AUFTRAG
    ,APLATZ_ID
    ,CONVERT(VARCHAR(10), ANFZEIT, 103) AS 'Fecha Inicio'
    ,RIGHT(ANFZEIT, 7) AS 'Hora Inicio'
    ,CONVERT(VARCHAR(10), ENDZEIT, 103) AS 'Fecha Fin'
    ,RIGHT(ENDZEIT, 7) AS 'Hora fin'
    ,ABS(DATEDIFF(minute, RIGHT(ANFZEIT, 7), RIGHT(ENDZEIT, 7))) AS 'MinutosTranscurridos'
    ,ZEIT
    ,LEFT(APLATZ_ID, 5) AS 'Maquina'
    ,LEFT(AUFTRAG, 2) AS 'Turno'
FROM BEAS_ARBZEIT

Where I am occupying

ABS(DATEDIFF(minute, RIGHT(ANFZEIT, 7), RIGHT(ENDZEIT, 7))) as 'MinutosTranscurridos'

ABS to convert the data to positive and the DATEDIFF to get the minutes elapsed, but I have a small detail.

When the range is within the same day during the 24 hours it sends me the correct range of minutes elapsed, but when the range covers two days it sends me a wrong time.

Examples:

19/04/2017     1:00AM    19/04/2017     6:00AM    300 MIN  "esto es ok"

18/04/2017    10:00PM    19/04/2017     1:00AM    1260 MIN  "ESTO NO ESTA BIEN"

Update based on the responses received

I am restructuring the query with something similar to what it says @Patricio Moracho I am restructuring the query with something similar to what @Patricio Moracho comments

DECLARE @ANFZEIT DATETIME
DECLARE @ENDZEIT DATETIME 
SET @ANFZEIT = 'select ANFZEIT from BEAS_ARBZEIT';
SET @ENDZEIT = 'select ENDZEIT from BEAS_ARBZEIT';
select BELNR_ID,BELPOS_ID,DocDate,AUFTRAG,APLATZ_ID,
        CONVERT(VARCHAR(10),ANFZEIT,103) AS 'Fecha Inicio', 
        RIGHT(ANFZEIT, 7) AS 'Hora Inicio',
        CONVERT(VARCHAR(10),ENDZEIT,103) AS 'Fecha Fin', 
        RIGHT(ENDZEIT, 7) AS 'Hora fin',
        DATEDIFF(MINUTE,@ANFZEIT,@ENDZEIT) as 'MinutosTranscurridos',
        ZEIT,LEFT(APLATZ_ID,5) AS 'Maquina',LEFT(AUFTRAG,2) AS 'Turno' from BEAS_ARBZEIT

but this error marks me

La conversión falló al convertir la fecha y / o
el tiempo de una cadena de caracteres.

sql

asked by antonio sanchez 19.04.2017 в 16:27

source

4 answers

Someone knows why the Java IDE returns an error when I convert from String to GregorianCalendar how to know how many words a String has in php?

score 2 · Answer 1

The solution is simpler than what seems to be the problem, it's just a matter of putting the difference of the dates with:

DATEDIFF(mi, ANFZEIT, ENDZEIT) AS 'MinutosTranscurridos'

And if it is necessary to handle the negatives, it would be with:

ABS(DATEDIFF(mi, ANFZEIT, ENDZEIT)) AS 'MinutosTranscurridos'

Finally, your query would be as follows:

SELECT BELNR_ID
    ,BELPOS_ID
    ,DocDate
    ,AUFTRAG
    ,APLATZ_ID
    ,CONVERT(VARCHAR(10), ANFZEIT, 103) AS 'Fecha Inicio'
    ,RIGHT(ANFZEIT, 7) AS 'Hora Inicio'
    ,CONVERT(VARCHAR(10), ENDZEIT, 103) AS 'Fecha Fin'
    ,RIGHT(ENDZEIT, 7) AS 'Hora fin'
    ,ABS(DATEDIFF(mi, ANFZEIT, ENDZEIT)) AS 'MinutosTranscurridos'
    ,ZEIT
    ,LEFT(APLATZ_ID, 5) AS 'Maquina'
    ,LEFT(AUFTRAG, 2) AS 'Turno'
FROM BEAS_ARBZEIT

Here you can see the demonstration of the solution

score 1 · Answer 2

As you can see, the fields ANFZEIT and ENDZEIT are already of date type, so it is not necessary to convert them to another type of data before obtaining the difference. Simply use:

ABS(DATEDIFF(minute, ANFZEIT, ENDZEIT)) as 'Minutos Transcurridos'

Since as mentioned by @PatricioMoracho in his answer, when using RIGHT(ANFZEIT, 7) he first converts the date and time to a string of characters according to the standard format for the regional configuration and then he takes the last 7 characters, to then return to convert to type DATETIME but without the part of the date, so the schedules are the same day (depending on the database manager used can be 1900-01-01 or something similar) and then the result.

score 0 · Answer 3

Try the following:

ABS(DATEDIFF(MINUTE, CAST(CONVERT(CHAR(16), ANFZEIT,120) AS SMALLDATETIME),
   CAST(CONVERT(CHAR(16), ENDZEIT, 120) AS SMALLDATETIME)) as 'MinutosTranscurridos'

Note: It is understood that by default the BD manages military time (24:00, that is, 1:00 PM returns it as 1:00 PM, otherwise increase Char (16) depending on the length of characters)

score 0 · Answer 4

I'm re-structuring the query with something similar to what @Patricio Moracho comments:

DECLARE @ANFZEIT DATETIME
DECLARE @ENDZEIT DATETIME 
SET @ANFZEIT = 'select ANFZEIT from BEAS_ARBZEIT';
SET @ENDZEIT = 'select ENDZEIT from BEAS_ARBZEIT';
select BELNR_ID,BELPOS_ID,DocDate,AUFTRAG,APLATZ_ID,
        CONVERT(VARCHAR(10),ANFZEIT,103) AS 'Fecha Inicio', 
        RIGHT(ANFZEIT, 7) AS 'Hora Inicio',
        CONVERT(VARCHAR(10),ENDZEIT,103) AS 'Fecha Fin', 
        RIGHT(ENDZEIT, 7) AS 'Hora fin',
        DATEDIFF(MINUTE,@ANFZEIT,@ENDZEIT) as 'MinutosTranscurridos',
        ZEIT,LEFT(APLATZ_ID,5) AS 'Maquina',LEFT(AUFTRAG,2) AS 'Turno' from BEAS_ARBZEIT

But this error marks me:

The conversion failed to convert the date and / or time of a string of characters.