Trigger to subtract dates

Question

Trigger to subtract dates

Navigation

#1 by (2 votes)

0

Hello colleagues I have this query that works well to find the difference between two dates in days, hours, minutes and seconds but when I pass it to the trigger it does not work anymore I get a number 4873 I do not know what I'm doing wrong, my column differs It's varchar type.

 select
    datediff(second, firstdate, lastdate),
    datediff(second, firstdate, lastdate) / 86400 as d,
    datediff(second, firstdate, lastdate) / 3600 as h,
    (datediff(second, firstdate, lastdate) % 3600) / 60 as m,
    (datediff(second, firstdate, lastdate) % 3600) % 60 as s
    from tiempos

5145    0   1   25  45
1695    0   0   28  15
4024    0   1   7   4

CREATE TRIGGER TGTIEMPOSRESTADEFECHAS
ON PRUEBAS 
AFTER UPDATE
AS BEGIN
UPDATE f
set f.diferencia = datediff(second, f.firstdate, f.lastdate)+
datediff(second, f.firstdate, f.lastdate) / 86400 +
datediff(second, f.firstdate, f.lastdate) / 3600 +
(datediff(second, f.firstdate, f.lastdate) % 3600) +
(datediff(second, f.firstdate, f.lastdate) % 3600) 
from inserted i inner join  Tiempos f on i.TestID=f.TestID
where f.diferencia is null end

2017-07-04 11:10:58.447 2017-07-04 12:18:02.930     4873

sql sql-server tsql sql-server-2014 trigger

asked by Raul.Sen 05.07.2017 в 16:20

source

1 answer

Prevent the app activity from resuming by rotating the [duplicate] screen Print an array of dates

score 2 · Accepted Answer

Ok, what you want is to concatenate your results, which is different from adding them:

CREATE TRIGGER TGTIEMPOSRESTADEFECHAS
ON PRUEBAS 
AFTER UPDATE
AS 
BEGIN

    UPDATE f
    SET f.diferencia = CONCAT(DATEDIFF(SECOND,f.firstdate,f.lastdate)/86400,'d,',
                              DATEDIFF(SECOND,f.firstdate,f.lastdate)/3600,'h:',
                              (DATEDIFF(SECOND,f.firstdate,f.lastdate)%3600)/60,'m:',
                              (DATEDIFF(SECOND,f.firstdate,f.lastdate)%3600)%60,'s')
    FROM inserted i 
    INNER JOIN Tiempos f 
        ON i.TestID = f.TestID
    WHERE f.diferencia IS NULL;

END