Returning rows where date is...

[Aimless]

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

[PFMaBiSmAd]

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.)

[Aimless]

I've already got 250 rows of date's stored in a text format. Is there absolutely no way to do this?

[PFMaBiSmAd]

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.

[Aimless]

Thanks for your help.

[PFMaBiSmAd]

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)