On Wed, 2003-10-08 at 09:52, shaun thornburgh wrote: > 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
It would be a good idea to format your SQL so it can be read more easily, I am sure people would be more inclined to help you if you did this. I am sure PHP has date time functions that help with this. Have a look at the online manual or download it. Can't quite remember but I think there is an hour between function, go to php.net and look at the date/time function bit. If not try looking on the net for someone who has already written such a function, there probably is someone who has. Ben -- **************************************************************** * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builder http://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * **************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
