SQL for last month and last two months

Navigation
1

Hello. I'd like some help here. I need to get the records:
MySQL Database
1) last month 2) last 2) 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, template 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

    
asked by anonymous 30.05.2017 / 13:56

2 answers

3

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())
    
30.05.2017 / 14:45
1

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

3) For the last two months you may have to have some more checking, but this can 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())

    
30.05.2017 / 14:57
How do I generate random numbers between 5 and 30 in Java? How do I set an equal value for a property of an object on all objects in a list?