CODEWITHSUNDEEP
mysqlvariables
Nike Yulistia Angreni
Nike Yulistia Angreni

Reputation: 311

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?

Upvotes: 2

Views: 278

Answers (1)

A J
A J

Reputation: 4024

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.

Upvotes: 3

Related Questions