Find certain dates in VARCHAR field in SQL (Mysql)

Asked

Viewed 3,229 times

10

I have the following condition:

WHERE  sv_users.userDataNascimento LIKE '%95%';

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

I arrive in the year through this calculation:

idadeDigited - Anocurrent = Anodenation

So I set up the consultation. So far so good.

But, if I want to search in this field a date that is smaller than, or larger than the numeral obtained in the calculation, for example, dates (VARCHAR) ending with the last two smaller numerals than the one resulting from the calculation (which in this case is 95), how do I? What I use?

mysql sql quest varchar
  • 1

    It is possible to convert this field to date? with varchar you will have trouble sorting dates. To compare the year of a date date use year(). ex: select year(now())

3 answers

11


Try using a 4-digit date

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

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

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

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

This could be partly circumvented with some additional logic, considering a base year and assuming that none of the birth dates will be very old. But run away from those whims.

Anyway, using years of 4 digits the query would be simpler and we could use the function YEAR 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 really want to check the year in a field VARCHAR, considering the format dd/mm/aaaa or dd/mm/aa, an option would be the function RIGHT 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.

  • I made some adjustments to the answer, because the last example was not functional.

  • 1

    You forgot the case of someone who was born in 2002 on the right with 2 houses, didn’t you? (maybe the problem is already in the question)

  • @Bacco Well, it’s a great idea to make that clear. When I said that "it would be better to calculate four-digit years", I did not want to cite specific examples or cover all cases. But now that you’ve spoken, I see it’s really worth mentioning.

  • 4

    If you want you can add a factor (epoch) on both sides and use module to fix it, but it gets complex. Maybe it is the case of a comment. What I agree is that varchar gives more headache than anything.

  • @Bacco I agree. I added some comments in the reply. Every time I think of this solution using two houses, I seem to see a worse problem.

8

If it is not possible to change the type of the column of varchar for date, str_to_date() can get around this situation.

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

Example in sqlfiddle

5

Use the function date_format() Mysql, where something like:

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

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

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

Look at the documentation link for further explanation.

Browser other questions tagged mysql sql quest varchar

You are not signed in. Login or sign up in order to post.