So, I have employee contract data that will expire, I want to display the employee data for today's month and date for the next 3 months. I've tried using DATEADD or DATE_ADD but it still doesn't work. Please help

This is my query:

SELECT a.fullname, a.employee_id, a.id_number, b.date_contract_from, b.date_contract_to AS expired,                                                                           
       b.is_current, b.is_deleted, b.employment_status 
FROM hr_employees a
INNER JOIN hr_employee_jobs b
ON a.id = b.employee                                                     
WHERE b.date_contract_to >= NOW()
AND b.employment_status IN ('1', '2')
AND b.is_current = 1
AND b.is_deleted = 0  AND a.is_deleted = 0
GROUP BY b.employee
ORDER BY b.date_contract_to ASC

This is an example of how it looks. So, the expiry date in April should not appear.

this is the query view

CodePudding user response：

You can use date between in your Query and DATE_ADD function like this.

SELECT a.fullname, a.employee_id, a.id_number, b.date_contract_from, b.date_contract_to AS expired,                                                                           
       b.is_current, b.is_deleted, b.employment_status 
FROM hr_employees a
INNER JOIN hr_employee_jobs b
ON a.id = b.employee                                                     
WHERE b.date_contract_to BETWEEN now() AND  DATE_ADD(now(), INTERVAL 3 MONTH)
AND b.employment_status IN ('1', '2')
AND b.is_current = 1
AND b.is_deleted = 0  AND a.is_deleted = 0
GROUP BY b.employee
ORDER BY b.date_contract_to ASC