How to select data from the previous month and year?

Asked

Viewed 510 times

0

I’m trying to search from dates in Mysql.

I need to obtain the total sum of a value where the year and month are smaller than those reported in the variable:

SELECT SUM(valor_pag) FROM controle WHERE MONTH(data_paga) < 07 and YEAR(data_paga) <= 2017

But he’s ignoring months like: 08/2016, 09/2016, 10/2016, 11/2016 and 12/2016, because the month is conditioned to be less than 07 in MONTH(data_paga) < 07.

My intention is to get all the previous month’s records back, you understand? But the query understands that it should ignore months older than 7, why 08/2016, 09/2016, 10/2016, 11/2016 and 12/2016, or even 08/2015, 09/2015, 10/2015, 11/2015 and 12/2015 are out and so on

What could be wrong?

php mysql sql web-application select

  • https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_subdate tente data_paga <= subdate(curdate(),inteval 1 Month)

2 answers

1

There’s only one "OR" missing there:

SELECT SUM(valor_pag) FROM controle 
    WHERE (MONTH(data_paga) < 07 and YEAR(data_paga) <= 2017) or
          (YEAR(data_paga) < YEAR(now()))

0

There is another alternative that would use the first day of the month and then get only what is previous, for example using the MAKEDATE():

SELECT SUM(valor_pag)
 FROM controle
  WHERE data_paga < MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW()) - DAYOFMONTH(NOW()) + 1)

Another option would be to use the LAST_DAY() thus:

SELECT SUM(valor_pag)
 FROM controle
  WHERE data_paga <= LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH))

Browser other questions tagged php mysql sql web-application select

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