date range in mySQL not working

Hello all. This simple query is not retrieving any records in mySQL: SELECT * FROM agentdaily where dateWorked BETWEEN '10/01/2010' AND '02/01/2011' The data type for dateWorked in varchar.

I suspect the issue is that it is a varchar......thus "BETWEEN" is not processing the way you would expect a date to be processed If you think about it......what is between 2 strings......what falls between "cat" and "dog"? ;-) Can you change the data type for dateWorked? Cheers On Fri, 2011-02-11 at 11:38 -0500, Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

>>BETWEEN '10/01/2010' AND '02/01/2011' >>The data type for dateWorked in varchar. You cannot use this date format in a text field if you need to sort it or compare ranges. You need to use at least some "yyyy mm dd" format or better a true date format in the database. In your example '10/01/2010' is alphabetically greater than  '02/01/2011' although it is chronologically before, and there cannot be nothing in between.

Thanks all. I will change the data type and see if that helps

Try SELECT * FROM agentdaily where dateWorked BETWEEN '2010-10-01' AND '2011-02-01' On 2/11/2011 11:38 AM, Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

That didn't work either. ----- Excess quoted text cut - see Original Post for more -----

Date should be 'YYYYMMDD'. Leave out the dashes. Ray Thompson Tau Beta Pi Headquarters 865-546-4578 www.tbp.org That didn't work either. >Try > >SELECT * FROM agentdaily where dateWorked BETWEEN '2010-10-01' AND '2011-02-01' > > > > >On 2/11/2011 11:38 AM, Torrent Girl wrote: >>

Well that would only work IF your dates are stord in that format.....but even then it's a bit weak. The real solution is to change the dates to dates ;-) Cheers On Fri, 2011-02-11 at 12:52 -0500, Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

Another solution that might work (leaving dateWorked as varchar) SELECT * FROM agentdaily where STR_TO_DATE(dateWorked,'%M/%d/%Y') BETWEEN .... As long as the date is in the format of mm/dd/yyyy that should convert the varchar output to a date format, and allow the Between to work correctly. Again, SHOULD.... Problem is if you changed the type from varchar to date, unless you reload the data, the data that is in the table will be in internal date format, and probably was being ignored. George ----- Excess quoted text cut - see Original Post for more -----