I would like to know if there is any way mysql
to remove Sundays from a date range, that is,
Fecha inicIal : 2017-08-01 ( Y-m-d)
Fecha Final : 2017-08-31 ( Y-m-d)
number of days : 31
number of Sundays : 4
total :27
Also Know the number of Days that a month contains from a date, that is
Fecha inicIal : 2017-08-01 ( Y-m-d)
number of days:31
The function
DAYOFWEEK(date)
receives a date as a parameter, and returns a number indicating the day. According to the documentation, the numbering is as follows:...
Thus, a simple query could have an exclusion clause similar to the following:
Your question is very interesting and can lead to others such as how many Saturdays are there in an interval? or how many business days are there in it? There are several ways to solve it, in principle I can think of the following based on the calculation of multiples. Let's see:
Suppose the following: we want to count how many Sundays there are between 8/7/2017 (Monday) and 8/21/2017 (also Monday). To begin with, there are 15 days in total and the first is a Monday, so if we consider the Monday as day 1, the days of the range (counting from the from date) can be seen as the following set:
In this range, where Monday is the number 1 in the week, Sunday is 7, and locating Sunday is as simple as calculating the multiples of 7. The effective calculation is: the integer part of the division 15 / 7 = 2. There are actually 2 Sundays (days 7 and 14).
The only case that breaks this strategy is when the lower limit is already a Sunday, then in 15 days, instead of having 2 Sundays we will have 3, for example, in the following list of days of the week (from 6/8 /2017 to 8/20/2017), if we consider the day of the week of each one and start from 7 (Sunday) it would be like this:
There are still 15 days but there are three multiples of 7: 7, 14, 21, that is, 3 Sundays, so we will have to adjust this case to the initial logic
Return:
The most important of all is to calculate the day of the week of the lower limit
@Minimo
asDAYOFWEEK
it considers that Sunday is day one, it must be adjusted to be 7. By configuring@DiaSemana
it, any of the days can be counted.On the other hand, to know the number of days between two dates, you can use the function
DATEDIFF
that we have already used, for example:In our example it will return
31
days, then with a simple subtraction we can run out of Sundays and conclude that there are 27 business days. And if you want to know how many days are left to reach the end of the month, usingLAST_DAY()
we have the last day of the month given a date, and withDATEDIFF
we can obtain the number of days left until the end of the month, for example:For the first case, calculating the number of days between two dates except Sundays can be done by subtracting one day for every 7 days of a week, and one more if it starts on a Sunday, setting the result based on whether or not the condition is met. Keep in mind that when using the difference function, it takes into account the difference and not the total number of days, so the data must be corrected by adding one day.
The second question, number of days from a date to the end of the month, can be solved by calculating the last day of the month for a date with
LAST_DAY
and the difference between the two:In this last case, to calculate the number of days of a month in which a certain date is, you can use
DAY(LAST_DAY(FECHA))
.