MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


set @start = '2020-05-05'; -- Tuesday
set @end = '2020-06-29';  -- Monday
-- expected answer:  8 Fridays (check with a real calendar)

-- my first attempt
select 
  @start,
  @end,
  floor(datediff(@end, @start)/7) as Fridays;


@start
@end
Fridays


2020-05-05
2020-06-29
7


-- using round may solve the problem
select 
  @start,
  @end,
  round(datediff(@end, @start)/7,0) as Fridays;


@start
@end
Fridays


2020-05-05
2020-06-29
8


-- but does this always work?
with recursive dates as (
  select 
     cast(@start as DATE) as FromDate,
     1 as NumberOfDays
  union all
  select 
     FromDate,
     NumberOfDays+1
  from dates where NumberofDays < DATEDIFF(@end,@start)+1
  )
select 
   FromDate,
   DAYNAME(FromDate) as NameFrom,
   DATE_ADD(FromDate, INTERVAL NumberOfDays DAY) as ToDate,
   DAYNAME(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY)) as NameTo,
   round(datediff(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY), FromDate)/7,0) as Fridays
from dates

-- conclusion no, it ToDate is a Firday, the count is wrong.


FromDate
NameFrom
ToDate
NameTo
Fridays


2020-05-05
Tuesday
2020-05-06
Wednesday
0

2020-05-05
Tuesday
2020-05-07
Thursday
0

2020-05-05
Tuesday
2020-05-08
Friday
0

2020-05-05
Tuesday
2020-05-09
Saturday
1

2020-05-05
Tuesday
2020-05-10
Sunday
1

2020-05-05
Tuesday
2020-05-11
Monday
1

2020-05-05
Tuesday
2020-05-12
Tuesday
1

2020-05-05
Tuesday
2020-05-13
Wednesday
1

2020-05-05
Tuesday
2020-05-14
Thursday
1

2020-05-05
Tuesday
2020-05-15
Friday
1

2020-05-05
Tuesday
2020-05-16
Saturday
2

2020-05-05
Tuesday
2020-05-17
Sunday
2

2020-05-05
Tuesday
2020-05-18
Monday
2

2020-05-05
Tuesday
2020-05-19
Tuesday
2

2020-05-05
Tuesday
2020-05-20
Wednesday
2

2020-05-05
Tuesday
2020-05-21
Thursday
2

2020-05-05
Tuesday
2020-05-22
Friday
2

2020-05-05
Tuesday
2020-05-23
Saturday
3

2020-05-05
Tuesday
2020-05-24
Sunday
3

2020-05-05
Tuesday
2020-05-25
Monday
3

2020-05-05
Tuesday
2020-05-26
Tuesday
3

2020-05-05
Tuesday
2020-05-27
Wednesday
3

2020-05-05
Tuesday
2020-05-28
Thursday
3

2020-05-05
Tuesday
2020-05-29
Friday
3

2020-05-05
Tuesday
2020-05-30
Saturday
4

2020-05-05
Tuesday
2020-05-31
Sunday
4

2020-05-05
Tuesday
2020-06-01
Monday
4

2020-05-05
Tuesday
2020-06-02
Tuesday
4

2020-05-05
Tuesday
2020-06-03
Wednesday
4

2020-05-05
Tuesday
2020-06-04
Thursday
4

2020-05-05
Tuesday
2020-06-05
Friday
4

2020-05-05
Tuesday
2020-06-06
Saturday
5

2020-05-05
Tuesday
2020-06-07
Sunday
5

2020-05-05
Tuesday
2020-06-08
Monday
5

2020-05-05
Tuesday
2020-06-09
Tuesday
5

2020-05-05
Tuesday
2020-06-10
Wednesday
5

2020-05-05
Tuesday
2020-06-11
Thursday
5

2020-05-05
Tuesday
2020-06-12
Friday
5

2020-05-05
Tuesday
2020-06-13
Saturday
6

2020-05-05
Tuesday
2020-06-14
Sunday
6

2020-05-05
Tuesday
2020-06-15
Monday
6

2020-05-05
Tuesday
2020-06-16
Tuesday
6

2020-05-05
Tuesday
2020-06-17
Wednesday
6

2020-05-05
Tuesday
2020-06-18
Thursday
6

2020-05-05
Tuesday
2020-06-19
Friday
6

2020-05-05
Tuesday
2020-06-20
Saturday
7

2020-05-05
Tuesday
2020-06-21
Sunday
7

2020-05-05
Tuesday
2020-06-22
Monday
7

2020-05-05
Tuesday
2020-06-23
Tuesday
7

2020-05-05
Tuesday
2020-06-24
Wednesday
7

2020-05-05
Tuesday
2020-06-25
Thursday
7

2020-05-05
Tuesday
2020-06-26
Friday
7

2020-05-05
Tuesday
2020-06-27
Saturday
8

2020-05-05
Tuesday
2020-06-28
Sunday
8

2020-05-05
Tuesday
2020-06-29
Monday
8

2020-05-05
Tuesday
2020-06-30
Tuesday
8


with recursive dates as (
  select 
     cast(@start as DATE) as FromDate,
     1 as NumberOfDays
  union all
  select 
     FromDate,
     NumberOfDays+1
  from dates where NumberofDays < DATEDIFF(@end,@start)+1
  )
select 
   FromDate,
   DAYNAME(FromDate) as NameFrom,
   DATE_ADD(FromDate, INTERVAL NumberOfDays DAY) as ToDate,
   DAYNAME(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY)) as NameTo,
   DAYOFWEEK(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY)) as DOW,
   round(datediff(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY), FromDate)/7,0)
     + CASE WHEN DayOfWEEK(DATE_ADD(FromDate, INTERVAL NumberOfDays DAY))=6 THEN 1 ELSE 0 END as Fridays
from dates


FromDate
NameFrom
ToDate
NameTo
DOW
Fridays


2020-05-05
Tuesday
2020-05-06
Wednesday
4
0

2020-05-05
Tuesday
2020-05-07
Thursday
5
0

2020-05-05
Tuesday
2020-05-08
Friday
6
1

2020-05-05
Tuesday
2020-05-09
Saturday
7
1

2020-05-05
Tuesday
2020-05-10
Sunday
1
1

2020-05-05
Tuesday
2020-05-11
Monday
2
1

2020-05-05
Tuesday
2020-05-12
Tuesday
3
1

2020-05-05
Tuesday
2020-05-13
Wednesday
4
1

2020-05-05
Tuesday
2020-05-14
Thursday
5
1

2020-05-05
Tuesday
2020-05-15
Friday
6
2

2020-05-05
Tuesday
2020-05-16
Saturday
7
2

2020-05-05
Tuesday
2020-05-17
Sunday
1
2

2020-05-05
Tuesday
2020-05-18
Monday
2
2

2020-05-05
Tuesday
2020-05-19
Tuesday
3
2

2020-05-05
Tuesday
2020-05-20
Wednesday
4
2

2020-05-05
Tuesday
2020-05-21
Thursday
5
2

2020-05-05
Tuesday
2020-05-22
Friday
6
3

2020-05-05
Tuesday
2020-05-23
Saturday
7
3

2020-05-05
Tuesday
2020-05-24
Sunday
1
3

2020-05-05
Tuesday
2020-05-25
Monday
2
3

2020-05-05
Tuesday
2020-05-26
Tuesday
3
3

2020-05-05
Tuesday
2020-05-27
Wednesday
4
3

2020-05-05
Tuesday
2020-05-28
Thursday
5
3

2020-05-05
Tuesday
2020-05-29
Friday
6
4

2020-05-05
Tuesday
2020-05-30
Saturday
7
4

2020-05-05
Tuesday
2020-05-31
Sunday
1
4

2020-05-05
Tuesday
2020-06-01
Monday
2
4

2020-05-05
Tuesday
2020-06-02
Tuesday
3
4

2020-05-05
Tuesday
2020-06-03
Wednesday
4
4

2020-05-05
Tuesday
2020-06-04
Thursday
5
4

2020-05-05
Tuesday
2020-06-05
Friday
6
5

2020-05-05
Tuesday
2020-06-06
Saturday
7
5

2020-05-05
Tuesday
2020-06-07
Sunday
1
5

2020-05-05
Tuesday
2020-06-08
Monday
2
5

2020-05-05
Tuesday
2020-06-09
Tuesday
3
5

2020-05-05
Tuesday
2020-06-10
Wednesday
4
5

2020-05-05
Tuesday
2020-06-11
Thursday
5
5

2020-05-05
Tuesday
2020-06-12
Friday
6
6

2020-05-05
Tuesday
2020-06-13
Saturday
7
6

2020-05-05
Tuesday
2020-06-14
Sunday
1
6

2020-05-05
Tuesday
2020-06-15
Monday
2
6

2020-05-05
Tuesday
2020-06-16
Tuesday
3
6

2020-05-05
Tuesday
2020-06-17
Wednesday
4
6

2020-05-05
Tuesday
2020-06-18
Thursday
5
6

2020-05-05
Tuesday
2020-06-19
Friday
6
7

2020-05-05
Tuesday
2020-06-20
Saturday
7
7

2020-05-05
Tuesday
2020-06-21
Sunday
1
7

2020-05-05
Tuesday
2020-06-22
Monday
2
7

2020-05-05
Tuesday
2020-06-23
Tuesday
3
7

2020-05-05
Tuesday
2020-06-24
Wednesday
4
7

2020-05-05
Tuesday
2020-06-25
Thursday
5
7

2020-05-05
Tuesday
2020-06-26
Friday
6
8

2020-05-05
Tuesday
2020-06-27
Saturday
7
8

2020-05-05
Tuesday
2020-06-28
Sunday
1
8

2020-05-05
Tuesday
2020-06-29
Monday
2
8

2020-05-05
Tuesday
2020-06-30
Tuesday
3
8

FromDate	NameFrom	ToDate	NameTo	Fridays
2020-05-05	Tuesday	2020-05-06	Wednesday	0
2020-05-05	Tuesday	2020-05-07	Thursday	0
2020-05-05	Tuesday	2020-05-08	Friday	0
2020-05-05	Tuesday	2020-05-09	Saturday	1
2020-05-05	Tuesday	2020-05-10	Sunday	1
2020-05-05	Tuesday	2020-05-11	Monday	1
2020-05-05	Tuesday	2020-05-12	Tuesday	1
2020-05-05	Tuesday	2020-05-13	Wednesday	1
2020-05-05	Tuesday	2020-05-14	Thursday	1
2020-05-05	Tuesday	2020-05-15	Friday	1
2020-05-05	Tuesday	2020-05-16	Saturday	2
2020-05-05	Tuesday	2020-05-17	Sunday	2
2020-05-05	Tuesday	2020-05-18	Monday	2
2020-05-05	Tuesday	2020-05-19	Tuesday	2
2020-05-05	Tuesday	2020-05-20	Wednesday	2
2020-05-05	Tuesday	2020-05-21	Thursday	2
2020-05-05	Tuesday	2020-05-22	Friday	2
2020-05-05	Tuesday	2020-05-23	Saturday	3
2020-05-05	Tuesday	2020-05-24	Sunday	3
2020-05-05	Tuesday	2020-05-25	Monday	3
2020-05-05	Tuesday	2020-05-26	Tuesday	3
2020-05-05	Tuesday	2020-05-27	Wednesday	3
2020-05-05	Tuesday	2020-05-28	Thursday	3
2020-05-05	Tuesday	2020-05-29	Friday	3
2020-05-05	Tuesday	2020-05-30	Saturday	4
2020-05-05	Tuesday	2020-05-31	Sunday	4
2020-05-05	Tuesday	2020-06-01	Monday	4
2020-05-05	Tuesday	2020-06-02	Tuesday	4
2020-05-05	Tuesday	2020-06-03	Wednesday	4
2020-05-05	Tuesday	2020-06-04	Thursday	4
2020-05-05	Tuesday	2020-06-05	Friday	4
2020-05-05	Tuesday	2020-06-06	Saturday	5
2020-05-05	Tuesday	2020-06-07	Sunday	5
2020-05-05	Tuesday	2020-06-08	Monday	5
2020-05-05	Tuesday	2020-06-09	Tuesday	5
2020-05-05	Tuesday	2020-06-10	Wednesday	5
2020-05-05	Tuesday	2020-06-11	Thursday	5
2020-05-05	Tuesday	2020-06-12	Friday	5
2020-05-05	Tuesday	2020-06-13	Saturday	6
2020-05-05	Tuesday	2020-06-14	Sunday	6
2020-05-05	Tuesday	2020-06-15	Monday	6
2020-05-05	Tuesday	2020-06-16	Tuesday	6
2020-05-05	Tuesday	2020-06-17	Wednesday	6
2020-05-05	Tuesday	2020-06-18	Thursday	6
2020-05-05	Tuesday	2020-06-19	Friday	6
2020-05-05	Tuesday	2020-06-20	Saturday	7
2020-05-05	Tuesday	2020-06-21	Sunday	7
2020-05-05	Tuesday	2020-06-22	Monday	7
2020-05-05	Tuesday	2020-06-23	Tuesday	7
2020-05-05	Tuesday	2020-06-24	Wednesday	7
2020-05-05	Tuesday	2020-06-25	Thursday	7
2020-05-05	Tuesday	2020-06-26	Friday	7
2020-05-05	Tuesday	2020-06-27	Saturday	8
2020-05-05	Tuesday	2020-06-28	Sunday	8
2020-05-05	Tuesday	2020-06-29	Monday	8
2020-05-05	Tuesday	2020-06-30	Tuesday	8