[SOLVED] MYSQL Date Range Search

[localidiot]

I am trying to query a date range against a datetime column, but I only want to query the moth day and year.

 

I am passing 2 parameters to the url to compare against the current row from the recordset.

 

However when I use the date_format function to change the column value to match the url parameter, my results do not seem to be right.

 

Instead i get the right results for the month and day, however i get all years.

 

Meaning that the url paramater value looks like, 11/09/1943 or 11%2F09%2F1943. Since my column type is datetime, i use the date_format(mycolumn, '%m/%d/%Y') to match my $_GET['urlparam']

 

These seems to work, however it does not filter out the years, i get a result of the range, but I get them for all years.

 

If anyone has any better practices for using a date url parameter against a mysql column type of datetime, i would be most greatful.

 

Thanks for your time.

-Local idiot.

[localidiot]

i have added some info, the sever version and the query, Localidiot is a newb.

 

Server Info: Server version: 5.0.51a-24+lenny1

 

 

 

 

SELECT *

FROM traffic_arrests

WHERE date_format(citation_date, '%m/%d/%Y') >= colname and date_format(citation_date, '%m/%d/%Y') <= colname2

ORDER BY citation_date ASC

[PFMaBiSmAd]

You can only do greater-than/less-than date comparisons when the format of the date is from left to right, YYYY-MM-DD, which is one of the reasons why a DATETIME data type is formated like it is. You need to get the values you are comparing with your DATETIME column into the YYYY-MM-DD format, not the other way around.

 

To just use the DATE part of a DATETIME value, use the mysql DATE() function in your query.

[localidiot]

Much appreciated. I think what I will do is explode the url date and pass the mysql statement yyyy-mm-dd.

 

Thanks much.