[SOLVED] date range query

[gevensen]

i cant seem to get a query working using  name and date range (between start and end date)

all values are in the tables mysql doesnt return an error

any ideas?

mysql 5

php 5.2.8

 

$sql_query="SELECT recnum FROM sc_income WHERE ( full_name = 'gary' AND ( service_date >= '01/01/2009' AND service_date <= '08/01/2009') ) ";

[PFMaBiSmAd]

You CANNOT do greater-than/less-than date comparisons using that format '01/01/2009' because the fields that make up that format are not ordered left-to-right, MSD (Most Significant Digit - YEAR) to LSD (Least Significant Digit - DAY).

 

You need to use a DATE data type YYYY-MM-DD to get greater-than/less-than date comparisons to work. A DATE data type will also use less storage than the format you are currently using.

[JonnoTheDev]

01/01/2009 is not a valid date format. If you have service_date as a varchar field then you have made an error. It must be of type DATE (YYYY-MM-DD) to use operators on. Essentially this is a string '01/01/2009' so how can it be greater or less than another value?

[gevensen]

ok thats easy enough to convert thanks

somewhere deep down i knew that and couldnt remember it