CODEWITHSUNDEEP
sqlsql-servert-sqldate-differencedatetime2
K.Luth
K.Luth

Reputation: 135

SQL: Combine DATEDIFF from two columns and update the table with this time

I have recently started with SQL so I'm still learning, this is also my first question so sorry in advance.

I have a Table, Messages_History with three columns tmStartTime, tmEndTime and tmTotal, all data typeDATETIME2. 

tmStartTime                 tmEndTime
2018-12-18 13:38:02.054     2018-12-18 13:39:56.944
2018-12-18 13:20:06.927     2018-12-18 13:42:02.024
2018-12-18 13:10:51.450     2018-12-18 13:36:34.239
2018-12-18 13:09:54.005     2018-12-18 13:19:46.741
2018-12-18 13:05:40.865     2018-12-18 13:36:48.373
2018-12-18 13:04:05.028     2018-12-18 13:08:12.073

This can be over 10,000 rows long.

I want to calculate the difference between the tmStartTime and tmEndTime column in day hh:mm:ss.xxx How can I do this?

I have used DATEDIFF(datepart, tmStartTime, tmEndTime) name, to calculate the dateparts seperately and show them in the table. But how can i combine them in one column and update the tmTotal column with these values?

UPDATE Messages_History
SET tmTotals = DATEDIFF (second, tmStartTime, tmEndTime); <--- this was to try updating a column with only seconds datepart.
SELECT 
    MH.nId,
    MH.tmStartTime StartTime,
    MH.tmEndTime EndTime,
    MH.tmTotal Total,
    MH.tmTotals Totals,
FROM Messages_History MH

How can I combine the DATEDIFF so i can update my table column Column_Total?

Upvotes: 1

Views: 834

Answers (1)

PSK
PSK

Reputation: 17943

To update table you don't need SELECT like you are doing. You can achieve your update like following simple query.

UPDATE Messages_History
SET tmTotals =  
        CONVERT(varchar, DATEADD(second,  DATEDIFF(second, tmStartTime, tmEndTime), 0), 108) 
FROM Messages_History

Upvotes: 1

Related Questions