Nike Yulistia Angreni Nike Yulistia Angreni - 7 months ago 9
SQL Question

How To Get MySQL Variable Into Where Clause

I have query here:

select *,@final := DATE_ADD(start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where employee_type_id=2
and (end_date is null or end_date>now());




The result is:


enter image description here

But when i change my query into this:

select *,@final := DATE_ADD(start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where employee_type_id=2
and YEAR(@final)=2016
and (end_date is null or end_date>now());


It didn't give any result, it is null. What's wrong with my query and how to fix it?

A J A J
Answer

You can change your query in the following way

select *, DATE_ADD(emp.start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where emp.employee_type_id=2
and YEAR(DATE_ADD(emp.start_date, INTERVAL 2 YEAR))=2016
and (ISNULL(emp.end_date) or emp.end_date>now());

You must use ISNULL function to check if a particular column has NULL value.