Jump to content

Returning rows where date is...


Aimless

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.