By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 425,635 Members | 913 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Calculating time difference

 P: n/a Hi, I have a table called Bookings which has two important columns; Booking_Start_Time and Booking_End_Time. These columns are both of type DATETIME. Given any day how can I calculate how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day), can this be done with a query or do I have to work it out in my code? Thanks for your help Nov 12 '05 #1
Share this Question
1 Reply

 P: n/a Shaun Thornburgh wrote: Hi, I have a table called Bookings which has two important columns; Booking_Start_Time and Booking_End_Time. These columns are both of type DATETIME. Given any day how can I calculate how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day), can this be done with a query or do I have to work it out in my code? Thanks for your help -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 It can be done in a query. I assume there are other columns in the Bookings table that define what type of booking each record contains. But, I'll go w/ the specs you gave. Hopefully the DateTime columns have both the date and the time; otherwise, this could get ugly. PARAMETERS [Date?] DateTime; SELECT 8.5 - Sum(DateDiff("h", Booking_Start_Time, Booking_End_Time)) As Available FROM Bookings WHERE Booking_Start_Time Between [Date?] And DateAdd("n", -1, [Date?]+1) What it does: 8.5 - Sum(DateDiff("h", Booking_Start_Time, Booking_End_Time)) - since there are 8.5 hours between 9:00 and 17:30, just subtract the total booked hours from 8.5 to get the available hours. If you have another column like Room_Number (something that identifies what is stopping and starting on the DateTime values) you'd include it in the SELECT clause and create a GROUP BY clause like this: GROUP BY Room_Number The WHERE clause uses the date entered by the user when the query runs. It selects dates between the beginning of the Date (00:00:01) and the end of the Date ((Date + 1 Day) - 1 minute , which equals 23:59 of the Date). HTH, MGFoster:::mgf Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBP3nQboechKqOuFEgEQIHZwCgtDdrn3WH57Fpa6zx5Qkw12 mGz68An3SY 3mapIDQYaN7KJZ2UHKmzp8En =bWlP -----END PGP SIGNATURE----- Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.