a datetime MySQL query string...

[octane]

Hi, I'm trying to create a query string that searches between a set of dates [as chosen via a series of pop-up menus]

The string that I have so far is...

CREATE TEMPORARY TABLE ticket_time (serial int)

SELECT DISTINCT $table_ticket.t_time FROM $table_account, $table_ticket, $table_user
WHERE ($table_ticket.t_number = $table_account.a_number)
AND ($table_account.a_company = '$asset_with_for')
AND ($table_ticket.t_datetime BETWEEN '$asset_from_year-$asset_from_month-$asset_from_day%'
AND '$asset_to_year-$asset_to_month-$asset_to_day%')
AND ($table_user.clientcode = '$clientcode')

The bit that I'm stuck on is...

($table_ticket.t_datetime BETWEEN '$asset_from_year-$asset_from_month-$asset_from_day%' AND '$asset_to_year-$asset_to_month-$asset_to_day%')

Right now, this either grabs all or nothing; if you choose a date before any of the records in the table, you get nothing, but if you choose any 'from' or 'to' date inside the record dates then you get everything.

This isn't the ideal.

If anyone has any clue as to how to massage this query string into shape, seven virgins will await you in the afterlife.

 

[Corvus]

I have used a bit of SQL and have never seen this BETWEEN ... AND statement. Did you try:

$table_ticket.t_datetime >= '$asset_from_year-$asset_from_month-$asset_from_day%' AND $table_ticket.t_datetime <= $asset_to_year-$asset_to_month-$asset_to_day

 

[octane]

Thanks for that, but nope. It didn't work, or should I say it functioned in exactly the same way as the method I'm using at the moment.

I've got a feeling that it could be the wild card percent symbol that could be causing problems, but I'm not sure. The problem is, it's just not practical to specify an exact time as well as a date.

I've also tried a number of other date/time related MySQL functions and it seems that not a single one will work.

I might have to do the date/time calculations in PHP, which is a shame .. unless someone else can figure it out, that is?!.