help with comparing dates

ztron

can someone possibly help me out and tell me why this is returning no results when there are several items that contain dates within what i have specified.

/* Monthly enrollment search */ 
         $query = "Select dm_id, dm_list, dm_phone, dm_email, dm_sponsor_id, dm_start_date
		 		   FROM distributor_main
				   ORDER BY dm_sponsor_id DESC
				   WHERE DATE_FORMAT(dm_start_date, '%YYYY-%MM-%DD') >= '2003-11-01' && <= '2003-11-30'";
         display_search_results($query);

LordShryku

Instead of doing this

= '2003-11-01' && <= '2003-11-30'

Do this
BETWEEN '2003-11-01' AND '2003-11-30'

ztron

i tried that and it's still returning no results even though there are plenty of records between those dates.

ztron

found out that it's most likely not working because the program that enters the data into the database, market power, uses/is based on fox pro and stores the dates as strings. i'm done with work now so i'll have to figure it out later.

LordShryku

Look up CAST in the mysql manual. You may be able to cast that to a date field.

ztron

where exactly would i do the cast?
i know it would be something like this, but i'm not sure if it's going to work with the database or not.

cast(dm_start_date AS Date)

In the manual it says at the Select, but it doesn't work.