0
SELECT COUNT(DAYNAME='FRIDAY') WHERE date between 2020-05-05 AND 2020-06-09

Without Depend on any tables

I try to use build in function but I don't now how I get it

Stu
  • 30,392
  • 6
  • 14
  • 33
Mohamed adel
  • 1
  • 1
  • Something like: `select floor(datediff('2020-06-29','2020-05-05')/7);` but you might need to 1. What did you try ? – Luuk Nov 26 '22 at 13:34
  • Does this answer your question? [Count specific days of week between two dates](https://stackoverflow.com/questions/18460158/count-specific-days-of-week-between-two-dates) – Luuk Nov 26 '22 at 13:37
  • yes but I need to Count Fridays – Mohamed adel Nov 26 '22 at 14:03

1 Answers1

0

You have to be a little creative, see: DBFIDDLE

set @start = '2020-05-05'; -- Tuesday
set @end = '2020-06-29';  -- Monday

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

NOTE:

  1. The table is only used for testing.
  2. Some more testing might be needed to check if changing StartDate does work OK with all edge cases.

If testing all cases is OK you can do:

set @start = '2020-05-05'; -- Tuesday
set @end = '2020-06-29';  -- Monday

SELECT 
   @start as FromDate,
   @end as ToDate,
   round(datediff(@end, @start)/7,0)
     + CASE WHEN DayOfWEEK(@end)=6 THEN 1 ELSE 0 END as Fridays;
Luuk
  • 12,245
  • 5
  • 22
  • 33