none
Restar una hora a un rango de fechas RRS feed

  • Pregunta

  • Buenos dias :

    Estoy tratando de restar entre dos rangos de fechas una hora, osea si un empleado duro mas de una hora contar horas, minutos y segundos lo excedido, por ejemplo:

    11:31:01am - 12:34:03pm en este caso ya tengo una diferencia de 3 minutos y 2 segundos, en los demás casos que sea una hora o menos no poner nada.

    Alguien tiene idea como hacer estas diferencias, solamente si excede mas de una hora?

    Se los agradezcos de antemano.


    miércoles, 22 de marzo de 2017 16:20

Respuestas

  • Trata:

    DECLARE
    	@FromDate datetime = '1999-01-01T11:31:01',
    	@ToDate datetime = '1999-01-01T13:34:03';
    
    SELECT
    	R1.Duracion,
    	R2.h,
    	R2.m,
    	R2.s,
    	CASE WHEN R2.h < 1 THEN RIGHT('00' + CAST(R2.h AS varchar(2)), 2) ELSE CAST(R2.h AS varchar(10)) END + 'h:' +
    	RIGHT('00' + CAST(R2.m AS varchar(2)), 2) + 'm:' +
    	RIGHT('00' + CAST(R2.s AS varchar(5)), 2) + 's' AS duracion_hms
    FROM 
    	(
    	SELECT
    		CASE 
    		WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 
    		ELSE ABS(T.diff_sec) 
    		END AS Duracion
    	FROM
    		(
    		VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    		) AS T(diff_sec)
    	) AS R1
    	CROSS APPLY
    	(
    	SELECT
    		R1.Duracion / 3600 AS h,
    		(R1.Duracion % 3600) / 60 AS m,
    		(R1.Duracion % 3600) % 60 AS s
    	) AS R2;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marcado como respuesta ruyzz miércoles, 22 de marzo de 2017 17:53
    miércoles, 22 de marzo de 2017 17:45

Todas las respuestas

  • empieza por aquí

    select  datediff(mi ,cast('11:31:01am' as time),cast('12:34:03pm' as time)),
        datediff(mi ,cast('11:31:01am' as time),cast('12:34:03pm' as time))/60 as horas,
        datediff(mi ,cast('11:31:01am' as time),cast('12:34:03pm' as time))%60 as minutos


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 22 de marzo de 2017 16:31
    Moderador
  • Gracias por responder:

    Lo anterior solo hace resta para saber cuantos segundos, horas y minutos tienen, a continuacion muestra una funcion donde lo estoy aplicando a todos los empleados, el problema que tengo es el select case when pues si el primer rango de fechas que detecta es menor a 3600 segundos (1 hora) pone 0 en todos no entra en el else del case.

    CREATEFUNCTION [dbo].[fcSrhDifFechas]
    (
        @FromDate DATETIME,
        @ToDate DATETIME
    )
    RETURNS VARCHAR(80)
    AS
    BEGIN
        RETURN  (
                    SELECT  
                            CASE DATEPART(HOUR, Duration)
                                WHEN 1 THEN '1h:'
                                ELSE DATENAME(HOUR, Duration) + ' h:'
                            END       
                          +
                            CASE DATEPART(MINUTE, Duration)
                                WHEN 1 THEN '1m:'
                                ELSE DATENAME(MINUTE, Duration) + ' m:'
                            END  
                         +
                            CASE DATEPART(SECOND, Duration)
                                WHEN 1 THEN '1s'
                                ELSE DATENAME(SECOND, Duration) + 's'
                            END
                    FROM    (
                                
                                SELECT
    								CASE
    									WHEN DATEADD(SECOND, ABS(DATEDIFF(SECOND, @FromDate, @ToDate)), 0)  > 3600 THEN DATEADD(SECOND, 0, 0)
    									ELSE
    									DATEADD(SECOND, ABS(DATEDIFF(SECOND, @FromDate, @ToDate)), 0)
    								 END AS Duration
    
                            ) AS d
                )
    END

    Como repito no se porque unicamente selecciona un por ejemplo 

    WHEN DATEADD(SECOND, ABS(DATEDIFF(SECOND, @FromDate, @ToDate)), 0)  > 3600 THEN DATEADD(SECOND, 0, 0)

    y nunca entra en el ELSE.

    Si alguien sabe como mejorar esta funcion estaria agradecido.

    Saludos.

    miércoles, 22 de marzo de 2017 16:44
  • Trata:

    DECLARE
    	@FromDate datetime = '1900-01-01T11:31:01',
    	@ToDate datetime = '1900-01-01T12:34:03';
    
    SELECT
    	DATEADD(
    	SECOND,
    	CASE WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 ELSE ABS(T.diff_sec) END
    	, '19000101'
    	) AS Duracion
    	
    FROM
    	(
    	VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    	) AS T(diff_sec)


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    miércoles, 22 de marzo de 2017 17:02
  • Aca todo lo que hace la funcion:

    DECLARE
    	@FromDate datetime = '1900-01-01T11:31:01',
    	@ToDate datetime = '1900-01-01T12:34:03';
    
    SELECT
    	R1.Duracion,
    	R2.h,
    	R2.m,
    	R2.s,
    	CAST(R2.h AS varchar(10)) + 'h:' +
    	RIGHT('00' + CAST(R2.m AS varchar(2)), 2) + 'm:' +
    	RIGHT('00' + CAST(R2.s AS varchar(5)), 2) + 's' AS duracion_hms
    FROM 
    	(
    	SELECT
    		CASE 
    		WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 
    		ELSE ABS(T.diff_sec) 
    		END AS Duracion
    	FROM
    		(
    		VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    		) AS T(diff_sec)
    	) AS R1
    	CROSS APPLY
    	(
    	SELECT
    		R1.Duracion / 3600 AS h,
    		(R1.Duracion % 3600) / 60 AS m,
    		(R1.Duracion % 3600) % 60 AS s
    	) AS R2;

    Ten presente que la funcion DATEDIFF devuelve un entero por lo que si la diferencia en segundos es que el maximo entero permitido entonces la funcion dara error.

    Aca te dejo este articulo sobre el mismo tema, escrito por Itzik Ben-Gan.

    http://sqlmag.com/sql-server/how-compute-date-and-time-difference-parts


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Editado HunchbackMVP miércoles, 22 de marzo de 2017 17:22
    miércoles, 22 de marzo de 2017 17:14
  • Hola esta solución es casi lo que busco, como le excluiría la fecha, osea solo que quede; 00h:00m:00s

    En este caso 00h:03m:02s

    Gracias por ayudarme.

    miércoles, 22 de marzo de 2017 17:29
  • Chequeastes el segundo codigo que pegue?

    Tambien revisa el articulo del link si es posible que trabajes con eventos demasiado distantes.

    Si los eventos no distan mas de 24 horas entonces esto seria suficiente.

    DECLARE
    	@FromDate datetime = '1999-01-01T11:31:01',
    	@ToDate datetime = '1999-01-01T12:34:03';
    
    SELECT
    	R1.Duracion,
    	R2.h,
    	R2.m,
    	R2.s,
    	RIGHT('00' + CAST(R2.h AS varchar(2)), 2) + 'h:' +
    	RIGHT('00' + CAST(R2.m AS varchar(2)), 2) + 'm:' +
    	RIGHT('00' + CAST(R2.s AS varchar(5)), 2) + 's' AS duracion_hms
    FROM 
    	(
    	SELECT
    		CASE 
    		WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 
    		ELSE ABS(T.diff_sec) 
    		END AS Duracion
    	FROM
    		(
    		VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    		) AS T(diff_sec)
    	) AS R1
    	CROSS APPLY
    	(
    	SELECT
    		R1.Duracion / 3600 AS h,
    		(R1.Duracion % 3600) / 60 AS m,
    		(R1.Duracion % 3600) % 60 AS s
    	) AS R2;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Editado HunchbackMVP miércoles, 22 de marzo de 2017 17:41
    miércoles, 22 de marzo de 2017 17:39
  • Esta ultima solución es mejor, solo tiene el inconveniente que pone la diferencia de horas cuando tiene menor de una hora.

    En la misma tabla estoy utilizando la función que tenia antes, la ultima función solo resta una hora cuando tiene mas de 3600 segundos pero cuando tenga menos de una hora, tengo que poenr 00h:00m:00s. Lo que esta señalado como rojo a la derecha es la funcion que tenia, pero a la izquierda pone lo mismo, solo cuando tiene mas de una hora pone bien (en azul)

    Osea quiero que quede:

       00h:00m:00s

       0h:02m:29s

    Gracias por ayudarme porque esta solución es muy difícil.

    miércoles, 22 de marzo de 2017 17:42
  • Trata:

    DECLARE
    	@FromDate datetime = '1999-01-01T11:31:01',
    	@ToDate datetime = '1999-01-01T13:34:03';
    
    SELECT
    	R1.Duracion,
    	R2.h,
    	R2.m,
    	R2.s,
    	CASE WHEN R2.h < 1 THEN RIGHT('00' + CAST(R2.h AS varchar(2)), 2) ELSE CAST(R2.h AS varchar(10)) END + 'h:' +
    	RIGHT('00' + CAST(R2.m AS varchar(2)), 2) + 'm:' +
    	RIGHT('00' + CAST(R2.s AS varchar(5)), 2) + 's' AS duracion_hms
    FROM 
    	(
    	SELECT
    		CASE 
    		WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 
    		ELSE ABS(T.diff_sec) 
    		END AS Duracion
    	FROM
    		(
    		VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    		) AS T(diff_sec)
    	) AS R1
    	CROSS APPLY
    	(
    	SELECT
    		R1.Duracion / 3600 AS h,
    		(R1.Duracion % 3600) / 60 AS m,
    		(R1.Duracion % 3600) % 60 AS s
    	) AS R2;


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marcado como respuesta ruyzz miércoles, 22 de marzo de 2017 17:53
    miércoles, 22 de marzo de 2017 17:45
  • Excelente solución, no hubiera podido hacerlo solo, solo le hice un pequeño cambio, pero igual la solucion esta excelente:

    Dejo lo ultimo que modifique en mi BD:

    ALTER FUNCTION [dbo].[fcSrhDifFechas]
    (
        @FromDate DATETIME,
        @ToDate DATETIME
    )
    RETURNS VARCHAR(80)
    AS
    BEGIN
        RETURN  (
                   SELECT
    				CASE WHEN R2.h < 1 THEN RIGHT('00' + CAST(R2.h AS varchar(2)), 2) ELSE CAST(R2.h AS varchar(10)) END + 'h:' +
    										RIGHT('00' + CAST(R2.m AS varchar(2)), 2) + 'm:' +
    										RIGHT('00' + CAST(R2.s AS varchar(5)), 2) + 's' AS duracion_hms
    				FROM 
    				(
    					SELECT
    						CASE 
    						WHEN T.diff_sec > 3600 THEN T.diff_Sec - 3600 
    						ELSE ABS(T.diff_sec-T.diff_sec) 
    						END AS Duracion
    					FROM
    					(
    					VALUES(DATEDIFF(SECOND, @FromDate, @ToDate))
    					) AS T(diff_sec)
    				) AS R1
    	CROSS APPLY
    	(
    	SELECT
    		R1.Duracion / 3600 AS h,
    		(R1.Duracion % 3600) / 60 AS m,
    		(R1.Duracion % 3600) % 60 AS s
    	) AS R2
                )
    END

    A continuación una imagen a como lo quería

    La columna de la derecha era la función como yo lo había hecho, la de la izquierda la solución final.

    Saludos.

    miércoles, 22 de marzo de 2017 17:57