MYSQL: How to make a month closure from day 26 to day 26

Question

MYSQL: How to make a month closure from day 26 to day 26

Navigation

#1 by (4 votes)
#2 by (0 votes)

0

I have a service registration form completed, and at the end of the month I have to analyze the closing costs of the amount of services, and pay the service providers.

But the closing of the month is every 26th day, so how could I present only the services of the day (JANUARY 27 --- until --- FEBRUARY 26)?

follow the code below:

<?php$soma_custos=mysqli_query($con,"SELECT SUM(custosgerais) FROM custos WHERE MONTH(data_baixa) BETWEEN $mes AND $mes");  
?>

But the code above is only showing me the services from the 1st to the 30th of the same month.

I need to pick up from the 27th of last month until the 26th of the current month.

php mysql

asked by anonymous 25.11.2017 / 17:36

2 answers

How to transform a diagonal elements list from a python array [closed] How do I validate the radio input of my code?

score 4 · Answer 1

Just specify the day and so pick up everything between the dates:

SELECT SUM(custosgerais) 
FROM custos 
WHERE data_baixa >= '2017-01-26 00:00:00' AND data_baixa < '2017-02-26 00:00:00'

You can also use BETWEEN , if you prefer:

SELECT SUM(custosgerais) 
FROM custos 
WHERE (data_baixa BETWEEN '2017-01-26 00:00:00' AND '2017-02-25 23:59:59')

score 0 · Answer 2

The question "I need to get from the 27th of last month to the 26th of the current month ."

The where clause in this case is:

WHERE data_baixa >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-27')
AND data_baixa <= DATE_FORMAT(NOW() ,'%Y-%m-27')

In the example table below, what you want is the sum of the column custosgerais of the selected rows in blue, ie between the 27th of last month and the 26th of this month.

Bydoingtheselectwewillreturnthesum=122

So, just run the script on closing day to get the required sum. If you run before the closing day you will get the sum up to that day.