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

Help With a DATETIME Query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Is this an _Access_ question? If an Access/Jet database, take a look in Help
at the DateDiff function.
"Shaun" <sh***@mania.plus.com> wrote in message
news:fu*******************@wards.force9.net...
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

Nov 12 '05 #2

P: n/a
Thanks for your reply, but I am using mySQL 3.23 and DATEDIFF doesn't come
out until 4.11 in mySQL...
"Larry Linson" <bo*****@localhost.net> wrote in message
news:tH*****************@nwrddc01.gnilink.net...
Is this an _Access_ question? If an Access/Jet database, take a look in Help at the DateDiff function.
"Shaun" <sh***@mania.plus.com> wrote in message
news:fu*******************@wards.force9.net...
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


Nov 12 '05 #3

P: n/a
I think you are trying to do too much in one query. I suggest that you do
one query to find the booking hours in each day followed by another query
that sums the days of interest.
Hugh
"Shaun" <sh***@mania.plus.com> wrote in message
news:fu*******************@wards.force9.net...
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.