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
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure .
The DATEDIFF function can be used in the following versions of MySQL: MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1. 1.
DATE_SUB() function in MySQL is used to subtract a specified time or date interval to a specified date and then returns the date.
But if you still want to use DATEDIFF , you can write something like this: SELECT FROM_DAYS(DATEDIFF(NOW(),C. END_DT)) FROM mytable C; DATEDIFF here return the differences in total days and using FROM_DAYS(), it will return the total year, month and day from the specific date in the comparison.
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.
Have a look at the BETWEEN
operator.
expr BETWEEN min AND max
Another way is to use the DATE_SUB
operator
WHERE date_column > DATE_SUB(NOW(), INTERVAL 5 DAY)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With