SQL for last month and last two months

Asked

Viewed 2,991 times

5

Hi. I’d like a hand here. I need to get the records:
Mysql bank
1) last month 2) last quarter 3) Current year 

SELECT id, data, lote, modelo, qtd FROM controle_diario WHERE modelo like ?

The table is very simple: id is auto-increment, batch is varchar, model is varchar and Qtd is int. Example: 

id - data - modelo - qtd  
1 - 2017-04-20 - 10001 - 100  
2 - 2017-04-22 - 10002 - 10  
3 - 2017-05-10 - 100010 - 30  
4 - 2017-05-15 - 100020 - 60  
5 - 2017-05-16 - 100060 - 70  
6 - 2017-05-20 - 100010 - 100

Thank you

mysql sql database select date

  • Which bank is using ?

  • Which database are you using? You could edit your question by displaying some example table data or use something similar to http:/sqlfiddle.com/?

  • 1

    Okay. Edited Question!

  • From what I saw the field date is not of the type date or datetime, right?

  • I didn’t notice it there. But yes, the capo data is DATE. I transform into Java to go to the bank.

2 answers

8


Last month:

SELECT id, data, lote, modelo, qtd FROM controle_diario *WHERE MONTH(data)=(MONTH(NOW())-1)*

Current year:

SELECT id, data, lote, modelo, qtd FROM controle_diario WHERE YEAR(data)=YEAR(NOW())

Correction proposed by Mauro Lacerda for the Last Month query:

WHERE MONTH(data)=MONTH(ADDDATE(NOW(), INTERVAL -1 MONTH)) AND YEAR(data)=YEAR(ADDDATE(NOW(), INTERVAL -1 MONTH))

  • Thanks for the help Rovann Linhalis

  • Not at all. The part about the two-month period is a little subjective, because I can understand it as the first two months, January and February. But maybe your need is February and March. But using the month option you can do what you need.

  • Ok. I did what I needed here. I left the two months before my current date. Thank you

  • 3

    Last month: so it doesn’t work because if it’s January it will be bad: WHERE MONTH(data)=(MONTH(NOW())-1) what works right is like this: WHERE MONTH(data)=MONTH(ADDDATE(NOW(), INTERVAL -1 MONTH)) AND YEAR(data)=YEAR(ADDDATE(NOW(), INTERVAL -1 MONTH))

3

1)SELECT id, data, lote, modelo, qtd FROM controle_diario WHERE MONTH(data) = ADDDATE(NOW(), INTERVAL -1 MONTH)

3) For the past two months you may have to have some more checking, but this may give you an origin for the solution:

SELECT id, data, lote, modelo, qtd FROM controle_diario WHERE MONTH(data) >= ADDDATE(NOW(), INTERVAL -4 MONTH) AND MONTH(data) <= ADDDATE(NOW(), INTERVAL -2 MONTH)

3)SELECT id, data, lote, modelo, qtd FROM controle_diario WHERE YEAR(data) = YEAR(NOW())

  • Thank you Roberto Fagundes.

Browser other questions tagged mysql sql database select date

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