query to return a recordset between two times

[dcjones]

Hi all,

I have a table which contains a number of fields one of which (ScheduledTime) is a time field "00:00".

I am trying to write a query that will return a recordset where the time feild is equal to the current time minus 1 hour, and equal to the current time plus 6 hours.

i.e if the current time is 12:00, display all the records between 11:00 and 18:00.

The query I have been working on is as follows:

SELECT ScheduledTime, Flight, Logo, IATALookup, RemarksWithTime, Terminal  FROM LGWFlightStats24 WHERE NOW() - INTERVAL 1 HOUR <= ScheduledTime && ScheduledTime<= NOW() + INTERVAL 6 HOUR AND ArriveDepart = 'I'  AND FlightDisplay = 'Y' ORDER BY ScheduledTime ASC

At the moment this does not work because if the current time is greater than 18:00 no record are returned.

Can anyone see a way of getting a result.

Many thanks in advance.

[dagon]

worked on my little test, should at least give you the right functions to play with

SELECT ScheduledTime, Flight, Logo, IATALookup, RemarksWithTime, Terminal FROM LGWFlightStats24
WHERE
ScheduledTime BETWEEN SUBTIME('ScheduledTime','1:00') AND ADDTIME('ScheduledTime','6:00')
AND ArriveDepart = 'I' AND FlightDisplay = 'Y' ORDER BY ScheduledTime ASC

[dcjones]

Hi dagon,

I will give it a go. Thanks.