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

10

I have the following condition:

WHERE  sv_users.userDataNascimento LIKE '%95%';

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

I arrive in the year through this calculation:

  

ageSigned - Year Running = Year of Birth

That's where I put 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 smaller than the result of the calculation ( which in this case is 95 ), how do I? What do I use?

    
asked by anonymous 16.01.2014 / 13:07

3 answers

11

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 .

    
16.01.2014 / 13:28
8

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

    
16.01.2014 / 13:38
5

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.

    
16.01.2014 / 13:19