RE: Help With a DATETIME Query

[shaun thornburgh]

Thanks for your reply, but its not an option!


From: "Dan Greene" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>, 
<[EMAIL PROTECTED]>
Subject: RE: Help With a DATETIME Query
Date: Tue, 7 Oct 2003 16:41:04 -0400

I know it's not the answer you're looking for... :(  but dealing with 
overnights has caused me so much aggravation in past apps I've written, 
I've tended to make the client create two (or more) 'bookings' for the 
covered time... don't know if it's an option for you, but it's my 
$0.02.....



> -----Original Message-----
> From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 4:33 PM
> To: [EMAIL PROTECTED]
> Subject: Help With a DATETIME Query
>
>
> 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. The query is run for each day i.e day 1,
> day 2.... day
> 10.
>
> 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") )
>
> _________________________________________________________________
> On the move? Get Hotmail on your mobile phone
> http://www.msn.co.uk/msnmobile
>
>
> --
> 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]

_________________________________________________________________
Use MSN Messenger to send music and pics to your friends 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]