How to remove Sundays in a range of dates in Mysql?

Your question is very interesting and can lead to how, how many Saturdays are there in an interval? or how many working days are there in it? There are several ways to solve it, in principle the following one occurs based on the calculation of multiples. Let's see:

Let us suppose the following: we want to count how many Sundays there are between 7/8/2017 (Monday and 21/8/2017 (also Monday). To begin with are 15 days in total and the first is a Monday, so if we consider Monday as day 1, the days of the rank (counting from the date from) we can see them as the following set:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

In this range, where Monday is number 1 in the week, Sunday is 7, and placing Sunday is as simple as calculating multiples of 7. The effective calculation is: the whole part of 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 the 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:

7,8,9,10,11,12,13,14,15,16,17,18,19,20,21

They continue being 15 days but there are three multiples of 7: 7, 14, 21 that is to say 3 Sundays, reason why we will have to adjust this case to the initial logic

SET @FromDate      = '2017-08-01';
SET @ToDate        = '2017-08-31';
SET @DiaSemana     = 7; -- 7 domingo, 1 lunes
SET @Minimo        = CASE WHEN DAYOFWEEK(@FromDate)-1 = 0 THEN 7 ELSE DAYOFWEEK(@FromDate)-1 END;

SELECT datediff(@ToDate, @FromDate) DIV 7 + (CASE WHEN @Minimo = @DiaSemana THEN 1 ELSE 0 END) AS 'CantDias';

The return:

CantDias
========
4

The most important of all is to calculate the day of the week of the lower limit @Minimo as DAYOFWEEK consider that Sunday is day one, you have to adjust it to be the 7. Setting @DiaSemana can be counted any of the days.

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:

datediff(@ToDate, @FromDate)

In our example we will return 31 days, then with a simple subtraction we can stay without Sundays and conclude that there are 27 working days. And if you want to know how many days are left until the end of the month, using LAST_DAY() on the last day of the month given a date, and with DATEDIFF we will be able to obtain the amount of remaining days for the end of the month, for example:

datediff(LAST_DAY(@FromDate),@FromDate)

The DAYOFWEEK(date) function receives a date as a parameter, and returns a number that indicates the day. According to the documentation, the numeration is as follows:

...

With which, a simple query could have an exclusion clause similar to the following:

Where DAYOFWEEK(fecha) <> 1

For the first case, calculate 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 Sunday, establishing the result depending on whether it meets or not the condition. Keep in mind that when using the function of difference takes into account the difference and not the total days, so the data must be corrected by adding a day.

SELECT IF (DAYOFWEEK(FECHA_INICIO)=1,
    FLOOR(DATEDIFF(FECHA_FIN, FECHA_INICIO)) - FLOOR((DATEDIFF(FECHA_FIN, FECHA_INICIO)/7)),
    FLOOR(DATEDIFF(FECHA_FIN, FECHA_INICIO)) - FLOOR((DATEDIFF(FECHA_FIN, FECHA_INICIO)/7))+1
) AS TOTAL
FROM MITABLA

The second question, number of days from a date to the end of the month, can be resolved by calculating the last day of the month for a date with LAST_DAY and the difference between the two:

SELECT LAST_DAY(FECHA_INICIO),
DATEDIFF(LAST_DAY(FECHA_INICIO),FECHA_INICIO)
FROM MI_TABLA

In the latter case, to calculate the number of days of a month in which a certain date is found you can use DAY(LAST_DAY(FECHA)) .