sql query to fetch the records of next 30 days

Question

Here is my problem, I want to fetch next 30 days records from the table. I have a field in my table. For ex: In my table I have resource_date, In this column I have many records from 2013-02-05 to 2015-10-10. Say, If I logged into the website today(Today's Date is- 16/01/2015, It should fetch record for next 15 days and so on). How to do this? Thanks in advance

Answer 1

In MySQL, you can use the NOW() function to get the current DATETIME, and the INTERVAL keyword to get intervals of time.

So, to get the records where resource_date is within the next 30 days, you would use:

SELECT *
 FROM `my_table_name`
WHERE `resource_date` >= NOW()
  AND `resource_date` < NOW() + INTERVAL 1 MONTH
    ;

In practice, you should rarely use SELECT *, and you should consider adding a LIMIT to this query to prevent your application from returning a result set that is "too large".

Answer 2

One way to do it

SELECT * 
  FROM table1
 WHERE resource_date >= CURDATE() + INTERVAL 1 DAY -- skip today
   AND resource_date < CURDATE() + INTERVAL 17 DAY -- 15 days starting tomorrow

Here is a SQLFiddle demo

Answer 3

WHERE resource_date >= CURDATE() AND resource_date <= DATE_ADD(CURDATE(), interval 15 DAY)

Answer 4

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

...
WHERE
'resource_date'> NOW() AND
'resource_date'< DATE_ADD(NOW(), INTERVAL 31 DAY);

Careful I think now() does minutes and hours so you miss a portion of a day.