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 .