Anil Kumar Reddy Anil Kumar Reddy - 26 days ago 6
MySQL Question

MySQL - add two dates and set equal to some days

I want to check that lastdate to today is equal to 75 days or not. If it is equal then return 1 else return 0.

I tried below query which gives error-

Select (DATE_ADD(Last_date,CURDATE())=75 DAY) from assessment;


Please give me correct query to get the result-

Answer Source

You need to use CASE expression with DATEDIFF:

Select 
   CASE WHEN DATEDIFF(Last_date,CURDATE()) = 75 then 1 else 0 END AS col
from assessment

Note that: If last_date might be before or after the CUR_DATE, in this case you will get a negative results. Because:

DATEDIFF() returns expr1 − expr2 expressed as a value in days

So, you might need to get the absolute value of the difference using ABS:

Select 
   CASE WHEN ABS(DATEDIFF(Last_date,CURDATE()) = 75 then 1 else 0 END) AS col
from assessment