DATE FORMAT wont work as I want

checks month part but not year

[lethal0r]

can anyone tell me why this does not work

SELECT <fields here> WHERE DATE_FORMAT(field_date, '%m%Y') >= '042006' AND DATE_FORMAT(field_date, '%m%Y') <= '012007'

It produces no results. On the otherhand if I do

SELECT <fields here> WHERE DATE_FORMAT(field_date, '%m%Y') >= '042006' AND DATE_FORMAT(field_date, '%m%Y') <= '052007'

this does produce results - but only for between 042006 and 052006. In other words the year part of my query seems to be being ignored. Am using MySQL 5.0.20a.

[LifeinAsia]

It doesn't work because you're comparing character strings.
The character string '042006' is greater than '012007' so will never return anything. The character string '042006' is less than '052007' so will return character strings between those strings.

I don't know MySQL, but something along the following should work:
SELECT <fields here>
WHERE field_date BETWEEN '4/1/2006' AND '1/1/2007'

[lethal0r]

thanks for putting me on the right track, also thanks for the BETWEEN command :) I have it working now.

[centime]

okay, can I ask what command you used?,, i posted the above as you were telling that you got it working

[lethal0r]

sure, I now have it as

SELECT <fields here> WHERE field_date BETWEEN '20060401' AND '20070101'

Comparing dates doesnt work if you leave out the day (I had just '%m%Y' originally). Im actually comparing two dates that are just months and years, so i need to manually stick the day on the ends of both dates im checking and it works great.

[txbakers]

Another option is to convert the date to an integer YYYYMMDD which you can easily compare.