Aimless Posted May 2, 2011 Share Posted May 2, 2011 Hey there, I'm calculating the current date like so $currentdate = date('d-m-Y', strtotime('+0 days')); So let's say a MYSQL field has a date of : 25-04-2011 in the format d-m-Y, and I want to return all of the rows that have a date that is -100 to +10 days compared to $currentdate, how do I do this? Any help is much appreciated. Best Regards, Aimless Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted May 2, 2011 Share Posted May 2, 2011 Your first step would be to store your dates in the database table using a DATE data type, which has a yyyy-mm-dd foramt. That's what the DATE data type exists for. Your current format cannot be directly used in greater-than/less-than comparisons (a DATE data type can.) Quote Link to comment Share on other sites More sharing options...
Aimless Posted May 2, 2011 Author Share Posted May 2, 2011 I've already got 250 rows of date's stored in a text format. Is there absolutely no way to do this? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted May 2, 2011 Share Posted May 2, 2011 A) Backup your database (just in case). B) Alter your table and add a column with a DATE data type. C) Execute a single UPDATE query (through phpmyadmin or your favorite database tool) using the mysql STR_TO_DATE() function to populate the new DATE column from your existing dates. D) Modify any existing query that references your old date column to use the mysql DATE_FORMAT() function to change the yyyy-mm-dd format into your existing format. E) Modify any existing query that stores a date to use the mysql STR_TO_DATE() function to convert your incoming format into a yyyy-mm-dd format. F) After you have tested that your code uses the new DATE column, alter your table to remove the old date column. G) Done, and your queries that make use of the DATE values will be easier to write. Quote Link to comment Share on other sites More sharing options...
Aimless Posted May 2, 2011 Author Share Posted May 2, 2011 Thanks for your help. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted May 2, 2011 Share Posted May 2, 2011 After your values are in a date data type, the follow query (untested) will match rows 100 days before and 10 days after the current date - SELECT * FROM your_table WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 100 DAY) AND DATE_ADD(CURDATE(), INTERVAL 10 DAY) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.