CODEWITHSUNDEEP
sqlsql-servert-sqldate
Nickson
Nickson

Reputation: 135

Difference between two dates returned in hours,minutes,seconds

The following query returns dates in terms of hours,minutes,seconds.

SELECT LEFT(CONVERT(VARCHAR(10),  ModifiedOn- CreatedOn, 108), 8)
AS ResultTime from AssignedRoles

I have an extra field in the table AssignedRoles -"TurnAroundTime" where i would like to store the result generated from the query. Is this method effective, or is using a trigger a better option and if so kindly explain how to undertake it.

The database is a backend to an MVC C# application

Upvotes: 0

Views: 102

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40359

I agree with @GMB (+1). However, you are storing (or calculating) this value as a formatted character string. I recommend storing it as a TIME datatype value, as that will give you more flexibility and options in the future. Let the application using this data worry about the formatting, it will do a better job of it.

ALTER TABLE AssignedRoles add 
 turnAroundtime as CONVERT(TIME, ModifiedOn - CreatedOn)

Upvotes: 2

GMB
GMB

Reputation: 222662

I would recommend a computed column:

alter table AssignedRoles add 
    turnAroundtime as (left(convert(varchar(10), ModifiedOn - CreatedOn, 108), 8))

This gives you an always up-to-date value that you don't need to maintain manually, with almost zero extra code.

Upvotes: 1

Related Questions