How to calculate number of Sundays between two dates using mysql

Question

I need to find number of Sundays between two dates using mysql. I know how to do it using PHP But i need it to be calculated using mysql.

Answer 1

SET @START_DATE = '2014-01-22';
SET @END_DATE = '2014-06-29';
SELECT 
    ROUND((
      (unix_timestamp(@END_DATE) - unix_timestamp(@START_DATE) ) /(24*60*60)
      -7+WEEKDAY(@START_DATE)-WEEKDAY(@END_DATE)
    )/7)

 + if(WEEKDAY(@START_DATE) <= 6, 1, 0)
 + if(WEEKDAY(@END_DATE) >= 6, 1, 0) as Sundays;

Solution consist of 2 parts:

1. counts number of full weeks (ROUND part), obviously you'll have 7 Sundays in 7 weeks.
2. count days that are included into the not full week parts (first or last)

If you'll need to use it more than once you can wrap it into the function:

DROP function IF EXISTS `count_weekdays`;
DELIMITER $$
CREATE FUNCTION `count_weekdays` (startDate date, endDate date, wd int)
RETURNS INTEGER
BEGIN
RETURN ROUND((
      (unix_timestamp(endDate) - unix_timestamp(startDate) ) /(24*60*60)
      -7+WEEKDAY(startDate)-WEEKDAY(endDate)
    )/7)

 + if(WEEKDAY(startDate) <= wd, 1, 0)
 + if(WEEKDAY(endDate) >= wd, 1, 0);
END$$

DELIMITER ;

Then you will be able to use it like this:

SET @START_DATE = '2018-07-03';
SET @END_DATE = '2018-07-28';
select 
  count_weekdays(@START_DATE, @END_DATE, 6) as Sundays,

  count_weekdays(@START_DATE, @END_DATE, 5) 
+ count_weekdays(@START_DATE, @END_DATE, 6) as weekends;

Where 6 ~ number of weekday stands for Sunday, 5 ~ Saturday.

Answer 2

http://sqlfiddle.com/#!2/d41d8/50695

Maybe there will be no code on this link someday so I posted it here.

the upper link will show the number of the day and with date of the sundays. if you want to find any other day change the 1 at last to 2 for monday

select DATE_ADD('2012-12-15', INTERVAL ROW DAY) as Date,
row+1  as DayOfMonth from (
SELECT @row := @row + 1 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6) t1,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6) t2, 
(SELECT @row:=-1) t3 limit 31
) b
where 
DATE_ADD('2012-12-01', INTERVAL ROW DAY)
between '2012-12-01' and '2012-12-31'
and
DAYOFWEEK(DATE_ADD('2012-12-01', INTERVAL ROW DAY))=1