October 28th, 2013, 03:06 AM
PHP Search MySQL between 2 dates
I've hit a bit of a brick wall with a project and have spent most of the morning and afternoon diving through forums/stackoverflow and just can't seem to get this working.
Basically what i'm wanting to do is query a MySQL table and display all records within a specific date range.
When I set the database up (quite a while ago) I set the date column as VARCHAR instead of DATE (don't ask why, because I don't know myself)
I'm using the following:
SELECT * FROM tablename WHERE columnname BETWEEN $startdate AND $enddate
$startdate and $enddate are set just before the SELECT........... and for example look like this 01/07/2013 31/07/2013
The dates as I look in the database are currently set to this format: DD/MM/YYYY (ie the 7th of december 2012 would look like this 07/12/2012)
I've tried converting the few thousand records to YYYY-MM-DD with a UPDATE and then changing the column from VARCHAR to DATE but it makes all dates from this year (2013) jump up to 2020 and a bunch of whacky stuff.
I assume its something to do with the VARCHAR column?
Any help/guidance would be greatly appreciated.
October 28th, 2013, 05:48 AM
MySQL question -> MySQL forum. This has nothing to do with PHP. I'll ask the moderators to move it.
No. Your "DD/MM/YYYY" simply isn't a valid MySQL date format.
Originally Posted by ben.y
Create an additional DATE column, parse the VARCHAR stuff with STR_TO_DATE() and store the result in the new column.
October 28th, 2013, 11:51 PM
I ended up working this out about 2 mins after I posted this but the forums were online and then offline like a yoyo last night so I gave up trying to post and let everyone know not to worry.
Originally Posted by Jacques1