Fetch certain dates in VARCHAR field in SQL (MySQL)

Question:

I have the following condition:

WHERE  sv_users.userDataNascimento LIKE '%95%';

And this condition looks for a date ( 11/12/1995 ), of type VARCHAR and not Timestamp , a year ending with the numerals 95 . This numeral is the result of a calculation that I do in my code to find the year of birth of the user searched in the filter in a form.

I arrive at the year through this calculation:

AgeTyped – Current Year = YearOfBirth

That's why I set up the query. So far so good.

But, if I want to search in this field for a date that is less than, or greater than the numeral obtained in the calculation, for example, dates ( VARCHAR ) that end with the last two numerals less than the result of the calculation (which in this case is 95 ), how do I do? What do I use?

Answer:

Try using a 4 digit date

First, try storing the date in a date field ( DATE ). Then it would be better to calculate years with 4 digits to avoid confusion with people of old or young age.

This is because people over 100 or under 15 (considering the year 2014 as a base) would not appear correctly in the greater or lesser filter.

For example, if someone was born after 2000 and you only use the last two digits, the comparison anoNascimento < 0 would not return any date.

The same is true for older people. For example, if someone was born in * 1990 , a comparison by anoNascimento < 90 would not include years 2000 onwards.

This could be partially circumvented with some additional logic, considering a base year and assuming none of the birth dates will be very old. However, run away from these gimmicks.

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

WHERE YEAR(sv_users.userDataNascimento) > 1995;

And in this other one:

WHERE YEAR(sv_users.userDataNascimento) < 1995;

Double-digit comparison solution

On the other hand, if you really want to check the year in a VARCHAR field, considering the format dd/mm/aaaa or dd/mm/aa , one option would be the RIGHT function and convert the result to a number:

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

Or

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

Example in sqlfiddle .

Scroll to Top