I need to get the result from the table, which the date should be difference of 5 from the current date.
ie., specific_date
column is present in my table. The format of the date is YYYY-MM-DD
.
I need the query something like,
SELECT * FROM `table_name` WHERE DATEDIFF(NOW(), specific_date) < 5
It looks like you are trying to do this:
WHERE specific_date < (NOW() + 5 days)
First of all, think carefully about the boundary cases. These boundary cases can bite your ankles in SQL. Do you actually want
WHERE specific_date <= (NOW() + 5 days)
Do your specific_date
columns timestamps contain only days (that is dates with times equal to midnight) or do they contain dates and times? If you're going to get the results you want, you need to be careful about those details.
At any rate, try this:
WHERE specific_date <= DATE_ADD(NOW(), INTERVAL 5 DAY)
This is a good way to do such a lookup. The column value stands alone on one side of the inequality predicate (the <=
) so mySQL can do an index range scan if you have an index on the column.
Date arithmetic in MySQL is quite flexible. You can do
NOW() + INTERVAL 10 SECOND
NOW() - INTERVAL 2 MINUTE
NOW() + INTERVAL 4 HOUR
CURDATE() - INTERVAL 1 WEEK /* midnight one week ago */
LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH /*first day of present month*/
NOW() - INTERVAL 1 QUARTER
CURDATE() - INTERVAL 5 YEAR
and so forth.