Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

Diferencia de fechas y horas

Estas en el tema de Diferencia de fechas y horas en el foro de SQL Server en Foros del Web. Hola que tal, Tengo una función que toma 4 parámetros, Fecha y Hora inicial ; Fecha y Hora final y me da la diferencia en ...
  #1 (permalink)  
Antiguo 15/08/2012, 16:10
 
Fecha de Ingreso: junio-2009
Mensajes: 250
Antigüedad: 14 años, 9 meses
Puntos: 1
Diferencia de fechas y horas

Hola que tal,

Tengo una función que toma 4 parámetros, Fecha y Hora inicial ; Fecha y Hora final y me da la diferencia en horas pero en formato decimal, es decir si la diferencia es de 12:30 hrs me trae 12.5 y así sucesivamente. Pero no siempre me trae las diferencias correctas hay algunos casos en que me las trae mal como por ejemplo:

Del '20120712' con hora 1240 osea (12:40) al mismo día '20120712' pero de las 1600 osea (16:00) trae la diferencia mal, pero si la hora final cambia a 1610 (16:10) lo trae correctamente, ¿alguna idea del por qué? les dejo el código para ver si pueden encontrar algún error que yo no.

Muchas gracias de antemano.

Código SQL:
Ver original
  1. CREATE FUNCTION [dbo].[HoraInicialint]
  2.  
  3. (   @HISAP2 nvarchar(10),
  4.     @HFSAP2 nvarchar(10),
  5.     @FISAP DateTIME ,
  6.     @FFSAP DATEtime
  7.      )
  8.  
  9. RETURNS nvarchar(10)
  10. AS
  11. BEGIN
  12. DECLARE @HIFF VARCHAR (10)
  13. DECLARE @HFFF VARCHAR (10)
  14. DECLARE @DifDIas nvarchar(10)
  15. DECLARE @HP1 nvarchar (10)
  16. DECLARE @HP2 nvarchar (10)
  17. DECLARE @Salida nVarchar(20)
  18. DECLARE @HISAPV VARCHAR (10)
  19. DECLARE @HFSAPV VARCHAR (10)
  20. DECLARE @HISAP INT
  21. DECLARE @HFSAP INT
  22.  
  23.  
  24.     --COnversiond e valores hora  a int ----------------------------------------------
  25.     SET @HISAPV = CONVERT(FLOAT,@HISAP2 )
  26.     SET @HFSAPV = CONVERT(FLOAT,@HFSAP2 )
  27.     SET @HISAP = CONVERT(INT,@HISAPV)
  28.     SET @HFSAP = CONVERT(INT,@HFSAPV)
  29.    
  30.     SET @HISAP2 = ISNULL(@HISAP2,01)
  31.     SET @HFSAP2 = ISNULL(@HFSAP2,01)
  32.     SET @FISAP = ISNULL(@FISAP,0)
  33.     SET @FFSAP = ISNULL(@FFSAP,0)
  34.    
  35.    
  36.    
  37.     -- COnversion de hora inicial
  38.     IF LEN(@HISAP) = 4
  39.     SET @HIFF =  CONVERT(Nvarchar,@HISAP)
  40.  
  41.     IF LEN(@HISAP) = 1
  42.     SET @HIFF = ('000'+ CONVERT(Nvarchar,@HISAP))
  43.  
  44.     IF LEN(@HISAP) = 2
  45.     SET @HIFF = ('00'+ CONVERT(Nvarchar,@HISAP))
  46.  
  47.     IF LEN(@HISAP) = 3
  48.     SET @HIFF = ('0'+ CONVERT(Nvarchar,@HISAP))
  49.  
  50.     -- COnversion de hora FINAL
  51.     IF LEN(@HFSAP) = 4
  52.     SET @HFFF =  CONVERT(Nvarchar,@HFSAP)
  53.  
  54.     IF LEN(@HFSAP) = 1
  55.     SET @HFFF = ('000'+ CONVERT(Nvarchar,@HFSAP))
  56.  
  57.     IF LEN(@HFSAP) = 2
  58.     SET @HFFF = ('00'+ CONVERT(Nvarchar,@HFSAP))
  59.  
  60.     IF LEN(@HFSAP) = 3
  61.     SET @HFFF = ('0'+ CONVERT(Nvarchar,@HFSAP))
  62.    
  63.    
  64.     -- Conversion y calculo de horas ----------------------------------------
  65.    
  66.    
  67.     IF DATEDIFF(DAY,@FISAP,@FFSAP) >=1
  68.     BEGIN
  69.         SET @DifDIas = ((DATEDIFF(DAY,@FISAP,@FFSAP)*2400) - @HISAP )  + @HFSAP  
  70.             IF LEN(@DifDIas) = 1
  71.                 SET @DifDIas = ('000'+ CONVERT(Nvarchar,@DifDIas))
  72.             IF LEN(@DifDIas) = 2
  73.                 SET @DifDIas = ('00'+ CONVERT(Nvarchar,@DifDIas))
  74.             IF LEN(@DifDIas) = 3
  75.                 SET @DifDIas = ('0'+ CONVERT(Nvarchar,@DifDIas))           
  76.             IF LEN(@DifDIas) = 4                                           
  77.                 SET @DifDIas =  CONVERT(Nvarchar,@DifDIas)                 
  78.                 SET @HP1 = LEFT(@DifDias,2)
  79.             IF LEN(@DifDIas) = 5
  80.                 SET @HP1 = LEFT(@DifDias,3)
  81.             IF (@DifDIas > = 100 ) -- SI es mayor a una hora, se le suma el dia completo
  82.                 BEGIN
  83.                     SET @HP2 = RIGHT(@DifDias,2)
  84.                     IF @HP2 > = 61
  85.                     BEGIN
  86.                         SET @HP2 = @HP2 -40
  87.                         SET @Salida =   CONVERT(FLOAT,@HP1) + ROUND((CONVERT(FLOAT, @HP2))/60,2 )
  88.                     END
  89.                     IF @HP2 <= 60
  90.                         SET @Salida =   CONVERT(FLOAT,@HP1) + ROUND((CONVERT(FLOAT, @HP2))/60,2 )
  91.                         --Set @Salida =  @HP2--ROUND((CONVERT(FLOAT, @HP2))/60,2 )--@DifDIas--HP2
  92.                 END
  93.             IF (@DifDIas < = 99 )-- si es menor de una hora, no toma en cuenta el dia que paso
  94.                 BEGIN
  95.                     SET @HP2 = RIGHT(@DifDias,2)
  96.                     SET @Salida =  ROUND((CONVERT(FLOAT, (@HP2-40)))/60,2 )
  97.                 END
  98.                
  99.     END
  100.     IF  DATEDIFF(DAY,@FISAP,@FFSAP) = 0 --and  DATEDIFF(day,@FISAP,@FFSAP) >= 0
  101.     BEGIN
  102.         SET @DifDIas = (CONVERT(FLOAT,@HFSAP) - CONVERT(FLOAT,@HISAP))
  103.         IF @DifDIas NOT LIKE '%-%'
  104.             BEGIN
  105.                 IF LEN(@DifDIas) = 4
  106.                     SET @DifDIas =  CONVERT(Nvarchar,@DifDIas)
  107.                 IF LEN(@DifDIas) = 1
  108.                     SET @DifDIas = ('000'+ CONVERT(Nvarchar,@DifDIas))
  109.                 IF LEN(@DifDIas) = 2
  110.                     SET @DifDIas = ('00'+ CONVERT(Nvarchar,@DifDIas))
  111.                 IF LEN(@DifDIas) = 3
  112.                     SET @DifDIas = ('0'+ CONVERT(Nvarchar,@DifDIas))
  113.             SET @HP1 = LEFT(@DifDias,2)
  114.             SET @HP2 = RIGHT(@DifDias,2)
  115.             IF @HP2 <= 60
  116.                 SET @Salida =  CONVERT(FLOAT,@HP1)+ ROUND((CONVERT(FLOAT, @HP2))/60,2 )--
  117.             IF @HP2 >= 61
  118.                 BEGIN
  119.                     SET @HP2 = @HP2 -40
  120.                     SET @Salida = CONVERT(FLOAT,@HP1) + ROUND((CONVERT(FLOAT, @HP2))/60,2 )
  121.                 END
  122.             END -- endIF @DifDIas NOT LIKE
  123.     END
  124.    
  125.    
  126.    
  127.     IF DATEDIFF(DAY,@FISAP,@FFSAP) <=-1
  128.     BEGIN
  129.         SET @DifDIas = ((DATEDIFF(DAY,@FFSAP,@FISAP)*2400) -  @HFSAP )  +   @HISAP
  130.             IF LEN(@DifDIas) = 1
  131.                 SET @DifDIas = ('000'+ CONVERT(Nvarchar,@DifDIas))
  132.             IF LEN(@DifDIas) = 2
  133.                 SET @DifDIas = ('00'+ CONVERT(Nvarchar,@DifDIas))
  134.             IF LEN(@DifDIas) = 3
  135.                 SET @DifDIas = ('0'+ CONVERT(Nvarchar,@DifDIas))           
  136.             IF LEN(@DifDIas) = 4                                           
  137.                 SET @DifDIas =  CONVERT(Nvarchar,@DifDIas)                 
  138.                 SET @HP1 = LEFT(@DifDias,2)
  139.             IF LEN(@DifDIas) = 5
  140.                 SET @HP1 = LEFT(@DifDias,3)
  141.             IF LEN(@DifDIas) = 6
  142.                 SET @HP1 = LEFT(@DifDias,4)
  143.             IF (@DifDIas > = 100 ) -- SI es mayor a una hora, se le suma el dia completo
  144.                 BEGIN
  145.                     SET @HP2 = RIGHT(@DifDias,2)
  146.                     IF @HP2 > = 61
  147.                     BEGIN
  148.                         SET @HP2 = @HP2 -40
  149.                         SET @Salida =   (CONVERT(FLOAT,@HP1) + ROUND((CONVERT(FLOAT, @HP2))/60,2 ) )*-1
  150.                     END
  151.                     IF @HP2 <= 60
  152.                         SET @Salida =   (CONVERT(FLOAT,@HP1) + ROUND((CONVERT(FLOAT, @HP2))/60,2 ) )*-1
  153.                         --Set @Salida =  @HP2--ROUND((CONVERT(FLOAT, @HP2))/60,2 )--@DifDIas--HP2
  154.                 END
  155.             IF (@DifDIas < = 99 )-- si es menor de una hora, no toma en cuenta el dia que paso
  156.                 BEGIN
  157.                     SET @HP2 = RIGHT(@DifDias,2)
  158.                     SET @Salida = ( ROUND((CONVERT(FLOAT, (@HP2-40)))/60,2 ))*-1
  159.                 END
  160.                
  161.     END
  162. RETURN @Salida
  163.  END
  #2 (permalink)  
Antiguo 15/08/2012, 16:19
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 17 años, 7 meses
Puntos: 774
Respuesta: Diferencia de fechas y horas

Mucho codigo!!! :S que necesitas hacer? igual y se puede con menos codigo ;)
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 15/08/2012, 16:27
 
Fecha de Ingreso: junio-2009
Mensajes: 250
Antigüedad: 14 años, 9 meses
Puntos: 1
Respuesta: Diferencia de fechas y horas

Pues lo explique brevemente arriba del codigo sacar la diferencia en decimal de una fecha y hora inicial y fecha y hora final

Ejemplo:

De 20120814 a las 1530 (15:30 hrs) hasta el 20120815 400 (04:00 hrs) son 12:30 hrs y quiero que me devuelva 12.5, el código lo hace en la mayoría de los casos de manera correcta, pero en algunos casos no.
  #4 (permalink)  
Antiguo 16/08/2012, 07:08
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 19 años, 3 meses
Puntos: 38
Respuesta: Diferencia de fechas y horas

De acuerdo con Libras... toda esa funcionalidad ya esta codificada y probada en la funcion DATEDIFF...

solo hay que utilizarla de la forma que mejor nos convenga.. por ejemplo..

Código:
CREATE FUNCTION [dbo].[HoraInicialint2](   
    @FISAP DATETIME 
    ,@FFSAP DATETIME 
)
 
RETURNS NVARCHAR(10)
AS 
BEGIN
  RETURN CAST(CAST((DATEDIFF(MINUTE,0, @FFSAP) - 
                  DATEDIFF(MINUTE,0, @FISAP))/60.00 AS DECIMAL(9,6)) AS VARCHAR)
END
GO

SELECT dbo.[HORAINICIALINT2]('20120712 12:40', '20120712 16:00') [DIFERENCIA EN HRAS]
SELECT dbo.[HORAINICIALINT2]('20120712 12:40', '20120712 16:10') [DIFERENCIA EN HRAS]
SELECT dbo.[HORAINICIALINT2]('20120710 12:40', '20120712 16:00') [DIFERENCIA EN HRAS]

--
Y el resultado:

Código:
DIFERENCIA EN HRAS
------------------
3.333333

DIFERENCIA EN HRAS
------------------
3.500000

DIFERENCIA EN HRAS
------------------
51.333333
Saludos!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #5 (permalink)  
Antiguo 16/08/2012, 07:23
 
Fecha de Ingreso: junio-2009
Mensajes: 250
Antigüedad: 14 años, 9 meses
Puntos: 1
Respuesta: Diferencia de fechas y horas

Hola que tal, me funciona correctamente el código que acabas de poner, el problema es que sólo estas enviando 2 parametros tipo datetime, y lo que quisiera que haga es que tome 4 parametros, 2 datetime para la fecha y 2 nvarchar para las horas (1610, 1600, etc) y que haga la diferencia, no sé si se pueda modificar tu función para que acepte los 4 parametros.

Saludos.
  #6 (permalink)  
Antiguo 16/08/2012, 08:27
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 19 años, 3 meses
Puntos: 38
Respuesta: Diferencia de fechas y horas

Si, si se puede.. aunque dejarla genérica te da el beneficio de reutilizarla en cualquier otra aplicación y/o proceso ya que tendría una funcionalidad especifica...

El resto es solo manejo de cadenas que no tienen que ver con la funcionalidad principal.. p.ejemplo..

Código:
DECLARE @HISAP2 NVARCHAR(10) = '420'
       ,@FISAP DATETIME = '20120710'
       
SET @hisap2 = RIGHT('0000' + @hisap2, 4)

SELECT CAST( CONVERT(VARCHAR, @fisap,112) 
             + ' ' 
             + SUBSTRING(@hisap2,1,2) 
             + ':' 
             + SUBSTRING(@HISAP2,3,2)  AS DATETIME) [FISAP]
Y el resultado:
Código:
FISAP
-----------------------
2012-07-10 04:20:00.000
Saludos!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #7 (permalink)  
Antiguo 16/08/2012, 08:30
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 17 años, 7 meses
Puntos: 774
Respuesta: Diferencia de fechas y horas

Muy buen aporte Andres95 :)
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #8 (permalink)  
Antiguo 16/08/2012, 09:58
 
Fecha de Ingreso: junio-2009
Mensajes: 250
Antigüedad: 14 años, 9 meses
Puntos: 1
Respuesta: Diferencia de fechas y horas

Antes que nada muchas gracias por tu respuesta, adapte tu código al anterior y me dio esto:

Código SQL:
Ver original
  1. ALTER FUNCTION [dbo].[HoraInicialINT]
  2.  
  3. (   @HISAP2 nvarchar(10),
  4.     @HFSAP2 nvarchar(10),
  5.     @FISAP DateTIME ,
  6.     @FFSAP DATEtime
  7.      )
  8.  
  9. RETURNS nvarchar(10)
  10. AS
  11. BEGIN
  12.  
  13. -- COnversion de horaS
  14.        
  15. SET @hisap2 = RIGHT('0000' + @hisap2, 4)
  16. SET @HFSAP2 = RIGHT('0000' + @HFSAP2, 4)
  17.  
  18. SET @FISAP = CAST( CONVERT(VARCHAR, @fisap,112)
  19.              + ' '
  20.              + SUBSTRING(@hisap2,1,2)
  21.              + ':'
  22.              + SUBSTRING(@HISAP2,3,2)  AS DATETIME)
  23.  
  24. SET @FFSAP = CAST( CONVERT(VARCHAR, @FFSAP,112)
  25.              + ' '
  26.              + SUBSTRING(@HFSAP2,1,2)
  27.              + ':'
  28.              + SUBSTRING(@HFSAP2,3,2)  AS DATETIME)
  29.  
  30.   RETURN CAST(CAST((DATEDIFF(MINUTE,0, @FFSAP) -
  31.                   DATEDIFF(MINUTE,0, @FISAP))/60.00 AS DECIMAL(9,6)) AS VARCHAR)
  32. END
  33. GO

Pero al usar el siguiente query:

Código SQL:
Ver original
  1. SELECT dbo.[HORAINICIALINT]('20120710' ,'1240', '20120712', '1600') [DIFERENCIA EN HRAS]

Me devuelve este error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

¿Alguna idea de que esté mal en la función?

Saludos y gracias por la ayuda.
  #9 (permalink)  
Antiguo 16/08/2012, 10:50
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 17 años, 7 meses
Puntos: 774
Respuesta: Diferencia de fechas y horas

lo que esta mal es el formato de fecha que estas manejando, revisa como envias los parametros y como se estan convirtiendo los datos....
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #10 (permalink)  
Antiguo 16/08/2012, 11:05
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 19 años, 3 meses
Puntos: 38
Respuesta: Diferencia de fechas y horas



checa el orden de los parámetros que definiste en la función..
primero se espera las horas y luego las fechas..



Saludos!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #11 (permalink)  
Antiguo 16/08/2012, 11:15
 
Fecha de Ingreso: junio-2009
Mensajes: 250
Antigüedad: 14 años, 9 meses
Puntos: 1
Respuesta: Diferencia de fechas y horas

Es verdad haha que tonto de mi parte, muchas gracias a ambos ya quedó! se los agradezco en verdad.

Etiquetas: datediff, diferencia, horas
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 09:57.