Why my MySQL query does not return me any result although I can find them in the database?

Question

I am going to search for current(which means their to_date is 01/01/9999) staff numbers in department d005.

I tried to use the query below:

select emp_no 
from current_dept_emp 
where dept_no = 'd005' and date_format(to_date,'%d/%m/%y') = '01/01/9999';

It returns an empty result.

Then, I thought maybe "01/01/9999" is not a date, I can treat it as a string, I use the wildcard for strings instead of date like below:

select emp_no 
from current_dept_emp 
where dept_no = 'd005' and to_date like '%9999';

still returns nothing.

but the part of the table looks like:

What's wrong with my code?

Answer 1

You probably want to use '%d/%m/%Y' with a capital Y