CODEWITHSUNDEEP
sqlsql-servert-sql
SajidBp
SajidBp

Reputation: 63

How do I add time datatype column in SQL server

I have a table in sql server 2012,with column named Duration as Time datatype.i want to update this column base on difference of dates, by adding the difference to this duration column.how can I do this in SP.

ID    StartDate               EndDate                 Duration
1     2017-02-27 09:10:35     2017-02-27 09:25:35      00:15
2     2017-02-27 09:26:35   2017-02-27 09:36:35        00:25

Durtion always less than 24 hours.

Upvotes: 3

Views: 8158

Answers (4)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

None of the other answers attempt to SUM the duration which is what you expect.

DECLARE @TABLE TABLE (ID INT, StartTime DATETIME, EndTime DATETIME, Duration TIME);

INSERT INTO @TABLE VALUES (1, '02/28/2017 01:00','02/28/2017 06:30','');
INSERT INTO @TABLE VALUES (2, '02/28/2017 09:00','02/28/2017 23:40','');
INSERT INTO @TABLE VALUES (3, '03/01/2017 10:02','03/01/2017 21:53','');

UPDATE t
SET Duration=
    CONVERT(TIME,
        (
            SELECT DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', EndTime - StartTime)), '00:00:00.000')
            FROM @TABLE it
            WHERE it.EndTime<= t.EndTime
        )
    )
FROM @TABLE t;

SELECT * FROM @TABLE;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

One method is:

update t
    set duration = cast(dateadd(ms, datediff(ms, startdate, enddate), 0) as time);

Upvotes: 2

MJSL
MJSL

Reputation: 51

UPDATE [TABLE] SET [DURATION] = CONVERT(varchar(5),DATEADD(minute, DATEDIFF(MINUTE, StartDate, EndDate), 0), 114);

Upvotes: 0

3BK
3BK

Reputation: 1348

This is an easy one. Just use a simple arithmetic operation:

DECLARE @TABLE TABLE (Start DATETIME, Finish DATETIME, Duration TIME);

INSERT INTO @TABLE VALUES ('02/28/2017 08:00','02/28/2017 08:30','');
INSERT INTO @TABLE VALUES ('02/28/2017 09:00','02/28/2017 09:40','');
INSERT INTO @TABLE VALUES ('02/28/2017 10:02','02/28/2017 11:53','');
INSERT INTO @TABLE VALUES ('02/28/2017 11:56','02/28/2017 12:45','');
INSERT INTO @TABLE VALUES ('02/28/2017 13:45','02/28/2017 23:59','');

UPDATE @TABLE
SET Duration = Finish - Start;

SELECT * FROM @TABLE;

Returns:

Start                    Finish                 Duration
---------------------------------------------------------
28/02/2017 08:00:00      28/02/2017 08:30:00    00:30:00
28/02/2017 09:00:00      28/02/2017 09:40:00    00:40:00
28/02/2017 10:02:00      28/02/2017 11:53:00    01:51:00
28/02/2017 11:56:00      28/02/2017 12:45:00    00:49:00
28/02/2017 13:45:00      28/02/2017 23:59:00    10:14:00

The only caveat here being that they need to be on the same day. You explicitly stated that the duration is never more than one day, so that should be fine.

If you want to add the result to the original value of Duration, then you would just add it on...

INSERT INTO @TABLE VALUES ('02/27/2017 08:00','02/28/2017 08:30','00:15');
INSERT INTO @TABLE VALUES ('02/28/2017 09:00','02/28/2017 09:40','00:14');
INSERT INTO @TABLE VALUES ('02/28/2017 10:02','02/28/2017 11:53','00:13');
INSERT INTO @TABLE VALUES ('02/28/2017 11:56','02/28/2017 12:45','02:16');
INSERT INTO @TABLE VALUES ('02/28/2017 13:45','02/28/2017 23:59','00:17');

UPDATE @TABLE
SET Duration = Duration + (Finish - Start);

Returns:

Start                    Finish                 Duration
---------------------------------------------------------   
27/02/2017 08:00:00      28/02/2017 08:30:00    00:45:00
28/02/2017 09:00:00      28/02/2017 09:40:00    00:54:00
28/02/2017 10:02:00      28/02/2017 11:53:00    02:04:00
28/02/2017 11:56:00      28/02/2017 12:45:00    03:05:00
28/02/2017 13:45:00      28/02/2017 23:59:00    10:31:00

Upvotes: 1

Related Questions