Data Type Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data Type Problems

Author

Topic

Khriz
Starting Member

2 Posts

Posted - 2003-09-01 : 07:01:19

I'm trying to do a trigger that calculates the difference in hours between 2 datatime fields and represent it in decimal hours.

This is the code:

CREATE TRIGGER calculo_horas ON marcas AFTER INSERT,UPDATE AS

DECLARE @salida DATETIME
DECLARE @entrada DATETIME
DECLARE @salida2 DATETIME
DECLARE @entrada2 DATETIME
DECLARE @salida3 DATETIME
DECLARE @entrada3 DATETIME
DECLARE @tmp DATETIME
DECLARE @tmp1 INT
DECLARE @tmp2 INT
DECLARE @tmp3 INT
DECLARE @tmp4 INT
DECLARE @horas FLOAT
DECLARE @minutos FLOAT
DECLARE @segundos FLOAT

SELECT @salida3 = horasalida FROM inserted
SELECT @entrada3 = horaentrada FROM inserted

--Here I get only the time in the datetime field
SELECT @entrada = convert(datetime, @entrada3, 108)
SELECT @salida = convert(datetime, @salida3, 108)

--Here I make de diff and get only the time
SELECT @tmp = convert(datetime, @salida - @entrada, 108)

--And here I get the hours, minutes, and seconds of the diff
SELECT @tmp1 = datepart(hour, @tmp)
SELECT @tmp2 = datepart(minute, @tmp)
SELECT @tmp3 = datepart(second, @tmp)

--Here the @tmp2 has 30 units, and when I convert to float to make the division, the variable @minutos has 3 units ¡Here's my problem! The minutes always will be 0
SELECT @minutos = convert(float, @tmp2)
SELECT @minutos = @minutos / 60
SELECT @segundos = convert(float, @tmp3)
SELECT @segundos = @segundos / 3600

SELECT @horas = convert(float, @tmp1) + @minutos + @segundos
SELECT @tmp4 = numregistro FROM inserted
UPDATE marcas SET Horas = @horas, Tpresencia = ((@tmp1 * 60) + @minutos) WHERE numregistro = @tmp4

Can anyone help, I've tried everything, and sorry for my poor english

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 09:13:38

quote:
--Here I make de diff and get only the time
SELECT @tmp = convert(datetime, @salida - @entrada, 108)

--And here I get the hours, minutes, and seconds of the diff
SELECT @tmp1 = datepart(hour, @tmp)
SELECT @tmp2 = datepart(minute, @tmp)
SELECT @tmp3 = datepart(second, @tmp)

Maybe it should be (or something like this):

SELECT @tmp1 = datediff(hour, @entrada, @salida)
SELECT @tmp2 = datediff(minute, @entrada, @salida)
SELECT @tmp3 = datediff(second, @entrada, @salida)

???

Khriz
Starting Member

2 Posts

Posted - 2003-09-01 : 10:14:56

Thanks for answering but the result is as good as the other version, the problem is when I try to convert those results to float, it always results 0.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 12:55:23

In fact you say that

declare @m float -- why float? use decimal(n,p)
select @m=convert(float,30)/60
select @m

produces 0, but it results with 0.5

???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources