date range and time range

[ehpratah]

Hi i have this query that search data between two date range

select RA, Name, Model, Plate, Service, Grand_total, Payment_type from advance WHERE SDate BETWEEN '" . $from . "' AND '" . $to . "' ORDER BY Payment_type

which is working great but i need to alter it so that it will also search for specific time range between that date range but seems not working properly

"select RA, Name, Model, Plate, Service, Grand_total, Payment_type from advance WHERE SDate BETWEEN '" . $from . "' AND '" . $to . "' AND Time BETWEEN '" . $fromtime . "' AND '" . $totime . "' ORDER BY Payment_type

my table structure for SDate is date and for Time is time

what am i doing wrong..?

[pritaeas]

It's a logic error.

You are searching between two times for all days in the range. You want to search from a specific date and time up to another date and time.

[ehpratah]

hi thats what i keep on telling to my superior that its not possible to search between two date range with two diff time range. but he told me to find a way to make it happen such a pain...:((

[JorgeM]

So, here's another approach... use a select statement to select records from another select statement..For example..

select * from (select * from table1 where sDate BETWEEN 'mm-dd-yyyy' AND 'mm-dd-yyyy') where sTime BETWEEN 'hh.mm.ss' AND 'hh.mm.ss'

So this is just an example, but it should give you an idea on how to approach this.

[pritaeas]

It IS possible.

Are you searching from the first date and time to the second date and time?

You can convert both columns into one, or change the where.

CONCAT(`datefield`, ' ', `timefield`) AS `datetime`

[ehpratah]

hi guys so theres still hope for me.. i'll try what you both suggested and find a way to make it work.i'll be intouch if i have another question on this matter. until then thanks to both of you