SQL statement searching for times between

[bitli]

Hi all, I am trying to write a SQL statement that will return results that fall into a cewrtain time frame.

The problem is if I have times such as 10PM and 4AM, the result will be 4AM to 10PM, which is not what I want. Any idea how to fix this

something like this will not work if the time1 is 10PM and time2 is 4AM

Select * from meetings where time_ between time 1 and time2

will get me the meetings happened between 4AM and 10PM, same result with "<" or ">" searches too.

 

[lespaul]

what does your table look like. Do you have two fields in a single record: Time1 and Time2

we need more details about your structure, what you are trying to do and from where the time criteria is being supplied.

leslie

 

[bitli]

O.K I will be more clear.

I have one time field the database, if I want to get the records falling into a specific time range I write something like following.

Select * from table1 where time_field between a_time and b_ time

Works fine till I pick times such as 10PM and 2AM. When this is the case I get results representing 2AM to 10PM, much larger period of time than 10PM to 2AM.

 

[lespaul]

that's because you are spanning two different DATES when you are looking for times that are after a PM and before an AM.



Leslie

 

[scriverb]

The between function will only work when the beginning date/time is less than the ending date/time values. Because you are not designating a date with your time entries it is assuming that they are both the current date.

EXAMPLE:

Between 5/14/2004 10:00 pm and 5/14/2004 2:00 am

This example will be looking for records between 5/14/2004 2:00 am and 5/14/2004 10:00 pm.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]