Campo smallint en hora

[mksp]

Hola como estan? necesito de su valiosa ayuda, tengo un query en donde tengo que trabajar con 3 campos que son de tipo smallint pero que especifican horas:minutos pero sin los puntos, ejemplo de eso 1345. Mi problema es tener que convertirlos de tipo hora para poder hacer una operación matematica, (x+(z+y)) que especifican los 3 campos de hora.

Les agradecería muchisimo su ayuda.

Gracias

[vrenzo]

Porque no pones tu Query... para ver.. como vas... y entenderte/ayudarte...

[leonardo_josue]

Hola mksp:

Si entendí bien en tu tabla tienes datos como estos:

20 -> 00:20
130 -> 01:30
2205 -> 22:05

es decir, cada entero corresponde a una hora y lo que necesitas es sumar los campos, de tal manera que el resultado anterior sea 23:55 correcto???

El algoritmo que se me ocurre sería el siguiente:

1°. convertir tu número a varchar, rellenando con ceros a la izquierda para dejarlo de cuatro posiciones:

20 -> 0020
130 -> 0130
2205 -> 2205

SQL Server no tienen una función específica para hacer esto, pero podrías hacerlo así:

Código SQL:

DECLARE @campo1 SMALLINT;
SET @campo1 = 30
SELECT RIGHT('0000' + CAST(@campo1 AS VARCHAR(4)), 4)
 

Ahora bien, ya con el campo varchar a 4 posiciones podrías convertirlo a tipo fecha:

Código SQL:

DECLARE @campo1 VARCHAR(4);
SET @campo1 = '0030'
SELECT CONVERT(datetime, LEFT(@campo1, 2) + ':' + RIGHT(@campo1,2), 108)
 

Finalmente, ya con los tres campos tipo datetime puedes sumarlos y recuperar la hora, con la función convert para obtener sólo la hora:

Código SQL:

DECLARE @campo1 datetime;
SET @campo1 = getdate()
SELECT CONVERT(VARCHAR, @campo1, 108)
 

Igual y sería conveniente que programes esto en una función Smallint_a_Datetime, para poder aplicarlo en los tres campos.

Dale un vistazo a los códigos que te puse, intenta crear tu función y si tienes problemas lo comentas en el foro para ayudarte a finar los detalles que puedas tener.

Saludos
Leo.

[mksp]

Muchisimas gracias por su pronta respuesta.. Aqui va el query como lo tengo:

SELECT OSCL.callID, OSCL.customer, OSCL.custmrName, OSCL.createDate,
(
case
when (OSCL.createTime)=0
then '00' + LEFT(OSCL.createTime,0)+':'+'0'+RIGHT(OSCL.createT ime,1)
when LEN(OSCL.createTime)=1
then '000' + LEFT(OSCL.createTime,00)+':'+'0'+RIGHT(OSCL.create Time,1)
when LEN(OSCL.createTime)=2
then '00' + LEFT(OSCL.createTime,0)+':'+RIGHT(OSCL.createTime, 2)
when LEN(OSCL.createTime)=3
then '0' + LEFT(OSCL.createTime,1)+':'+RIGHT(OSCL.createTime, 2)
when LEN(OSCL.createTime)=4
then LEFT(OSCL.createTime,2)+':'+RIGHT(OSCL.createTime, 2)
end
) as createTime,

OSCL.closeDate,OCLG.ClgCode, OCLG.Recontact, OCLG.U_Tecnico1, OCLG.U_Tipo_Mant, OCLG.U_FechaAtenc,
(
case
when (OCLG.U_TimeViaje)=0
then '00' + LEFT(OCLG.U_TimeViaje,0)+':'+'0'+RIGHT(OCLG.U_Time Viaje,1)
when LEN(OCLG.U_TimeViaje)=1
then '000' + LEFT(OCLG.U_TimeViaje,00)+':'+'0'+RIGHT(OCLG.U_Tim eViaje,1)
when LEN(OCLG.U_TimeViaje)=2
then '00' + LEFT(OCLG.U_TimeViaje,0)+':'+RIGHT(OCLG.U_TimeViaj e,2)
when LEN(OCLG.U_TimeViaje)=3
then '0' + LEFT(OCLG.U_TimeViaje,1)+':'+RIGHT(OCLG.U_TimeViaj e,2)
when LEN(OCLG.U_TimeViaje)=4
then LEFT(OCLG.U_TimeViaje,2)+':'+RIGHT(OCLG.U_TimeViaj e,2)
end
) as U_TimeViaje,
(
case
when (OCLG.U_HoraLleg)=0
then '00' + LEFT(OCLG.U_HoraLleg,0)+':'+'0'+RIGHT(OCLG.U_HoraL leg,1)
when LEN(OCLG.U_HoraLleg)=1
then '000' + LEFT(OCLG.U_HoraLleg,00)+':'+'0'+RIGHT(OCLG.U_Hora Lleg,1)
when LEN(OCLG.U_HoraLleg)=2
then '00' + LEFT(OCLG.U_HoraLleg,0)+':'+RIGHT(OCLG.U_HoraLleg, 2)
when LEN(OCLG.U_HoraLleg)=3
then '0' + LEFT(OCLG.U_HoraLleg,1)+':'+RIGHT(OCLG.U_HoraLleg, 2)
when LEN(OCLG.U_HoraLleg)=4
then LEFT(OCLG.U_HoraLleg,2)+':'+RIGHT(OCLG.U_HoraLleg, 2)
end
) as U_HoraLleg,
(
case
when (OCLG.U_HoraIniAten)=0
then '00' + LEFT(OCLG.U_HoraIniAten,0)+':'+'0'+RIGHT(OCLG.U_Ho raIniAten,1)
when LEN(OCLG.U_HoraIniAten)=1
then '000' + LEFT(OCLG.U_HoraIniAten,00)+':'+'0'+RIGHT(OCLG.U_H oraIniAten,1)
when LEN(OCLG.U_HoraIniAten)=2
then '00' + LEFT(OCLG.U_HoraIniAten,0)+':'+RIGHT(OCLG.U_HoraIn iAten,2)
when LEN(OCLG.U_HoraIniAten)=3
then '0' + LEFT(OCLG.U_HoraIniAten,1)+':'+RIGHT(OCLG.U_HoraIn iAten,2)
when LEN(OCLG.U_HoraIniAten)=4
then LEFT(OCLG.U_HoraIniAten,2)+':'+RIGHT(OCLG.U_HoraIn iAten,2)
end
) as U_HoraIniAten,
(
case
when (OCLG.U_HoraFinAten)=0
then '00' + LEFT(OCLG.U_HoraFinAten,0)+':'+'0'+RIGHT(OCLG.U_Ho raFinAten,1)
when LEN(OCLG.U_HoraFinAten)=1
then '000' + LEFT(OCLG.U_HoraFinAten,00)+':'+'0'+RIGHT(OCLG.U_H oraFinAten,1)
when LEN(OCLG.U_HoraFinAten)=2
then '00' + LEFT(OCLG.U_HoraFinAten,0)+':'+RIGHT(OCLG.U_HoraFi nAten,2)
when LEN(OCLG.U_HoraFinAten)=3
then '0' + LEFT(OCLG.U_HoraFinAten,1)+':'+RIGHT(OCLG.U_HoraFi nAten,2)
when LEN(OCLG.U_HoraFinAten)=4
then LEFT(OCLG.U_HoraFinAten,2)+':'+RIGHT(OCLG.U_HoraFi nAten,2)
end
) as U_HoraFinAten,

(u_timeviaje+(u_horafinaten-u_horalleg)) as tiempoactividad

FROM OSCL INNER JOIN
OCLG ON OSCL.callID = OCLG.parentId AND OSCL.objType = OCLG.parentType


ORDER BY OCLG.U_Tecnico1, OCLG.Recontact


Los campos que estan dentro de los CASE son las horas que tengo que hacerle la operacion (u_timeviaje+(u_horafinaten-u_horalleg)) as tiempoactividad, los rellene con 0 y agregue los : para que parecieran las fechas. Ahora cuando aplique mi formula (u_timeviaje+(u_horafinaten-u_horalleg)) as tiempoactividad
el calculo no lo hace bien cuando son horas exactas 14:00 no lo calcula bien por el hecho de que tendria que restarle 60. Un ejemplo practico para que entiendan de que no me da correctamente es:

Formula: (u_timeviaje+(u_horafinaten-u_horalleg)) as tiempoactividad
u_timeviaje=00:40
u_horafinaten=12:40
u_horalleg=12:20
(00:40+(12:40-12:20))=00:40+00:20=00:60 que deberia mostrar 1:00 y otro caso es
u_timeviaje=08:00
u_horafinaten=18:00
u_horalleg=12:17
(08:00+(18:00-12:17))=08:00+05:83=13:83 cuando deberia mostrar 13:43

Aqui es donde ando estancada, de antemano muchisimas gracias, espero que me puedan orientar.

[leonardo_josue]

Compañer@ mksp:

te estás complicando la existencia, en primer lugar no es conveniente que pongas tanto código repetitivo en tus consultas, pues esto afecta en gran cantidad el rendimiento de tu consulta, es por eso que en mi post te digo:

sería conveniente que programes esto en una función Smallint_a_Datetime, para poder aplicarlo en los tres campos.

En cuanto a los cálculos, si tus campos ya están como date no deberías tener problemas, la conversión la hace SQL Server, aquí está el ejemplo con los datos que pones y la salida correcta,

Código SQL:

DECLARE @TuTabla TABLE (u_timeviaje datetime, u_horafinaten datetime, u_horalleg datetime)
INSERT INTO @TuTabla VALUES ('1900-01-01 00:40', '1900-01-01 12:40', '1900-01-01 12:20');
INSERT INTO @TuTabla VALUES ('1900-01-01 08:00', '1900-01-01 18:00', '1900-01-01 12:17');
SELECT 
CONVERT(VARCHAR, u_timeviaje, 108) u_timeviaje,
CONVERT(VARCHAR, u_horafinaten, 108) u_horafinaten,
CONVERT(VARCHAR, u_horalleg, 108) u_horalleg,
CONVERT(VARCHAR, (u_timeviaje+(u_horafinaten-u_horalleg)), 108) tiempoactividad
FROM @TuTabla T;
 

Código:

u_timeviaje                    u_horafinaten                  u_horalleg                     tiempoactividad
------------------------------ ------------------------------ ------------------------------ ------------------------------
00:40:00                       12:40:00                       12:20:00                       01:00:00
08:00:00                       18:00:00                       12:17:00                       13:43:00

TRATA DE IMPLEMENTAR la función para convertir a fecha, olvídate del CASE, la función que te pongo yo sirve para cualquiera de los casos de puedas tener en tu tabla, agregando 1,2 o 3 ceros según se necesite.

Saludos
Leo

[mksp]

Lo de la programacion que me aconsejas pues no se como hacerla, no soy experta en sql, mas bien principiante.

aplicando esto como me dices

5.convert(varchar, u_timeviaje, 108) u_timeviaje,
6.convert(varchar, u_horafinaten, 108) u_horafinaten,
7.convert(varchar, u_horalleg, 108) u_horalleg,
8.convert(varchar, (u_timeviaje+(u_horafinaten-u_horalleg)), 108) tiempoactividad

me funciona a medias porque me trajo un valor 470.

En dado caso que quisiera aplicar por paso de primero hacerle la conversion a varchar y luego a date como podria aplicarlo todo en un select con los tres campitos que necesito? u_timeviaje, u_horafinaten, u_horalleg aparte de agregarle los 0 con los puntos que no tenga que colocar un case?.

Muchisimas gracias por tu ayuda.

[mksp]

Ayudaa por favorrr

[leonardo_josue]

Hola de nuevo mksp:

Te aseguro que "llorando" no vas a resolver tu problema, así es que evita post como el último que pones.

Dices que eres principiante, así es que lo primero que debiste hacer es leer algún manual para principiantes, la parte de las funciones y procedimientos almacenados en SQL Server y cualquier DBMS de permite desarrollar consultas muy complejas, algo que difícilmente podrás hacer con un simple SELECT.

Segundo, NUNCA ESPERES que aquí se te de la respuesta a tus tareas o trabajos y mucho menos que con un simple copiar y pegar obtengas la respuesta. La mayoría de las veces tendrás que hacer algún tipo de adecuación para que se adapte al 100% a tu realidad.

Tercero, lo que te publiqué en los dos primeros post's es prácticamente la respuesta a tu pregunta, me pregunto si al menos te tomaste un minuto para analizar y tratar de implementar la solución. Espero sinceramente que así haya sido. Ojo para la siguiente.

Ahora bien, volviendo con tu problema... Te comentaba que la parte más "compleja" podría ser la parte de convertir el smallint a datetime mediante una función, como dices ser principiante sería más o menos así:

Código SQL:

CREATE FUNCTION dbo.f_smallint_a_datetime 
(
    @fecha_smalltime SMALLINT   
)
RETURNS datetime
AS
BEGIN
  DECLARE @fecha_datetime datetime
  DECLARE @fecha_varchar VARCHAR(4)
  --Para rellenar a la izquierda con ceros
  SET @fecha_varchar = RIGHT('0000' + CAST(@fecha_smalltime AS VARCHAR(4)), 4)
  --Para convertir la cadena a datetime
  SET @fecha_datetime = CONVERT(datetime, LEFT(@fecha_varchar, 2) + ':' + RIGHT(@fecha_varchar,2), 108) 
  RETURN @fecha_datetime
END
 

Observa que el set @fecha_varchar y el set @fecha_datetime SON LAS MISMAS INTRUCCIONES QUE PUSE EN EL PRIMER POST, sólo cambiando el nombre de las variables.

Para ejecutarla sería así

Código SQL:

SELECT dbo.smallint_a_datetime(40);
 
/*
fecha
-----------------------
1900-01-01 00:40:00.000
*/
 

Ahora bien, SÓLO FALTARIA HACER EL CÁLCULO, invocando a esta función para los tres campos:

Código:

SELECT 
convert(varchar, aquí invocas la función, 108) u_timeviaje,
...
convert(varchar, (aquí invocas la función+(u_horafinaten-u_horalleg)), 108) tiempoactividad
FROM TuTabla

Haz el intento por obtener la consulta por tu propia cuenta, con todos los elementos que tienes podrás hacerlo, pero si continuas con problemas comenta en el post qué es lo que haz hecho, seguramente será poco lo que habrá que corregir.

Saludos
Leo.

[mksp]

Gracias por tu ayuda y por tu tiempo!..