Date Range Query problem

[joshtheflame]

Hi All,

 

i am developing a report in php using mysql. I have 53 records in my table which has a field "ticketdate" and it contains data like this "2009-10-01 16:12:01" but user wants to query only on date and input format is "01-10-2009" dd-mm-yy

..the problem is that following query is not pulling all the records rather it is skipping some records although date is in range.

   $squery = "SELECT * from ticketmaster where DATE_FORMAT(ticketdate, '%d-%m-%Y') between '$from_date' and '$to_date' and c_id = '$clientid' "; 

 

but when i put like this.

   $squery = "SELECT * from ticketmaster where c_id = '$clientid' "; 

 

it pulls all 53 records ...and I've noticed one thing is that if the data in table is from the date like

01-08-2009 till 29-2-2010 ...and if i give query parameters from date = 01-01-2009 and to_date = 01-06-2010 it says record not found...although it should bring records because they exist in between those dates..plz help whats the best practice for it.

[PFMaBiSmAd]

You can only do date comparisons with dates in the format YYYY-MM-DD (most significant field is year to least significant field day.)

 

Format the from and to dates as YYYY-MM-DD (see the mysql STR_TO_DATE() function) and then use the mysql DATE() function on the ticketdate field to only get the DATE part.

[joshtheflame]

PFMaBiSmAd you roxs mannnnnnnnnnnnn......great...it worked like a charm...thank you zillion times...