ansaurus

Question

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

Answer 1

+1 A:

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.

marc_s 2010-07-01 06:31:52

Thanx Marc_sIn the from what element do I use to fill in the time field..do I use datetimepicker or a text box? If I use a text box how can i convert it to time?Thanx

Allan 2010-07-01 06:57:22

@Allan: I would probably use a textbox, and maybe back it up with a Regex validation to make sure the user enters something in the form of `hh:mm:ss` into that textbox (and otherwise reject it as invalid)

marc_s 2010-07-01 08:22:40

ansaurus

tags:

views:

answers:

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

related questions