How to filter separated dates by day, month and year in MySQL

Question

How to filter separated dates by day, month and year in MySQL

Navigation

#1 by (5 votes)

6

Hello, I have a table "Meetings" in MySQL with the following structure:

id: Int (11), subject: String (40), day: String (2), month: String (2), year: String (4), active: String (2)

Save in the field day the day of the meeting, mes the corresponding number of the month of the meeting, and in year, the year that it occurs. for example: a meeting A that occurs on 04/20/2015 day = 20 month = 04 year = 2015

I would like to sort the result by proximity to the meeting, which is the one that is closest to occurring first. for this I used the following sql:

SELECT * FROM Reuniao ORDER BY ano ASC, mes ASC, dia ASC

It worked, but it also shows the meetings if they have already passed (first), but I need to show only the ones that are still going to happen. so I tried the following:

SELECT * FROM Reuniao WHERE ano >= 2015 AND mes >= 3 AND dia >= 3 ORDER BY ano ASC, mes ASC, dia ASC

supposing today was 03/03/2015 she should show all meetings that happen from now on, but presents an error, does not show meetings any meeting that happens before day 3 of any month, and month 3 of any year.

How to solve this? and also show only those that active="S"

mysql sql-select order-by

asked by anonymous 23.03.2016 / 04:22

1 answer

How to initialize an array in Kotlin? My factorial function does not return the expected value! C language

score 5 · Accepted Answer

The problems are:

1) Compare string with number

2) year > = 2015 AND mes > = 3 AND day > = 3, whereas both are integer is incorrect, for example if we have the values (2016, 2, 3) as the month is less the condition will fail.

And a solution:

Convert the separate fields to Date

     SELECT * FROM Reuniao
     WHERE str_to_date(concat_ws(',',dia,mes,ano),'%d,%m,%Y') >= curdate() AND ATIVA = 'S'
     ORDER BY ano ASC, mes ASC, dia ASC

Complementing with the question of taking the time into account you can do this:

To use string with time in str_to_date you only have to specify the format!

For example, with the hh: mm

     str_to_date(concat_ws(',',hora,dia,mes,ano),'%H:%i,%d,%m,%Y')