I'm under the impression that your over thinking the problem.
LOOK-UP the functions DATE_SUB / INTERVAL / TIME_TO_SEC / TO_DAYS and the arithmetic should be easy. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -->-----Original Message----- -->From: shaun thornburgh [mailto:[EMAIL PROTECTED] -->Sent: Wednesday, October 08, 2003 1:52 AM -->To: [EMAIL PROTECTED] -->Subject: Help With a DATETIME Query PLEASE! --> -->Hi, --> -->I have a table called Bookings which has two important columns; -->Booking_Start_Date and Booking_End_Date. These columns are both of type -->DATETIME. The following query calculates 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). However, when a booking spans more than one -->day the query doesn't work, for example if a user has a booking that -->starts -->on day one at 09.00 and ends at 14.30 on the next day, the query returns -->3.5 -->hours for both days. Any help here would be greatly appreciated. --> -->SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + -->DATE_FORMAT(B.Booking_End_Date, '%i')) - -->((DATE_FORMAT(B.Booking_Start_Date, -->'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS -->Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND -->B.Booking_Status <> '1' AND NOT ( '2003-10-07' < -->DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > -->DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) --> -->Thanks for your help --> -->_________________________________________________________________ -->Find a cheaper internet access deal - choose one to suit you. -->http://www.msn.co.uk/internetaccess --> --> -->-- -->MySQL General Mailing List -->For list archives: http://lists.mysql.com/mysql -->To unsubscribe: -->http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
