Search for certain dates in the VARCHAR field in SQL (MySQL)

Try using a 4-digit date

First, try storing the date in a date field (% with%). Then it would be best to calculate 4-digit years to avoid confusion with people of very young age.

This is because people over 100 or under 15 (considering 2014 as the base) would not appear correctly in the higher or lower filter.

For example, if someone was born from 2000 and you only used the last two digits, the DATE comparison would not return any dates.

The same goes for older people. For example, if someone was born in 1990, a comparison of% with% would not include the years 2000 and beyond.

This could be partially circumvented with some additional logic, considering a base year and assuming that none of the dates of birth will be very old. But flee these gambiarras.

Anyway, using 4-digit years the query would be simpler and we could use the anoNascimento < 0 as in the example below:

WHERE YEAR(sv_users.userDataNascimento) > 1995;

And in this other:

WHERE YEAR(sv_users.userDataNascimento) < 1995;

Two-digit comparison solution

On the other hand, if you want to even check the year in a anoNascimento < 90 field, considering the format YEAR or VARCHAR , an option would be the dd/mm/aaaa and convert the result for number:

WHERE CONVERT(RIGHT(sv_users.userDataNascimento, 2), SIGNED) > 95;

Or

WHERE CONVERT(RIGHT(sv_users.userDataNascimento, 2), SIGNED) < 95;

Example in sqlfiddle .

If you can not change the column type from varchar to date , str_to_date () can circumvent this situation.

SELECT str_to_date(userDataNascimento, "%d/%m/%Y") FROM tabela
WHERE year(str_to_date(userDataNascimento, "%d/%m/%Y")) < 1995

Example on sqlfiddle

Use the date_format () from MySQL, which would look something like this:

SELECT id
FROM sua_tabela
WHERE 95 > DATE_FORMAT(sv_users.userDataNascimento, '%y');

In the call, if you want the current date, put a NOW() or CURRENT_TIMESTAMP .

The second parameter is the format you want to return, %y is the year with two digits, %Y is the year with 4 digits.

Look at the documentation link for further explanation.