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?
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.
精彩评论