CODEWITHSUNDEEP
sqlsql-serverdatabase
Allan
Allan

Reputation: 57

Storing time values in a database and getting the difference between two time fields

I am working on an application the stores time values in a database e.g Expected time of Arrival and actual time of arrival. What is the best way of storing these fields in a database?

What SQL query can I use to obtain the time difference between the two fields?

Upvotes: 2

Views: 445

Answers (1)

marc_s
marc_s

Reputation: 755531

If you're on SQL Server 2008, use the TIME datatype. Declare those two fields

ExpectedTimeOfArrival TIME(7),
ActualTimeOfArrival TIME(7)

Read up on how to manipulate the TIME datatype here.

Once you have those two fields in place, you could add a computed column to your table that calculates the difference between those two, e.g. in minutes:

ALTER TABLE dbo.YourTable
   ADD TimeDifference AS DATEDIFF(MINUTE, ActualTimeOfArrival, ExpectedTimeOfArrival)

and then you could query o that new column TimeDifference as if it were a normal, regular table column.

Upvotes: 2

Related Questions