CODEWITHSUNDEEP
phpmysql
Mayur
Mayur

Reputation: 31

how to find working days and weekends in current month?

I want to find how many working days and weekends in a current month. using PHP and MySQL.

I have try below code :-

  select 5 * (DATEDIFF('2017-02-28', '2017-02-01') DIV 7) +  
  MID('0123444401233334012222340111123400001234000123440', 8 * WEEKDAY('2017-02-01') + WEEKDAY('2017-02-28') + 1, 1) as workingdays

Please give solution to find working days and weekends ,

Upvotes: 0

Views: 140

Answers (2)

Gmnayem
Gmnayem

Reputation: 111

 SELECT  5 * (DATEDIFF('2017-01-31','2017-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2017-01-01') + WEEKDAY('2017-01-31') + 1, 1)+1 workingDays FROM DUAL;

Hope it helps you.

Upvotes: 1

rescobar
rescobar

Reputation: 1305

Try this:

SELECT CASE WHEN WEEKDAY('2017-02-01') < 5 THEN 5 - WEEKDAY('2017-02-01') ELSE 0 END
+ CASE WHEN WEEKDAY('2017-02-28') < 5 THEN WEEKDAY('2017-02-28') + 1 ELSE 5 END 
+
(DATEDIFF('2017-02-28', '2017-02-01') + 1 
- (7 - WEEKDAY('2017-02-01')) 
- (WEEKDAY('2017-02-28') + 1)
) DIV 7 * 5 AS working_days;

Assuming that start_date: 2017-02-01 and end_date is 2017-02-28

Upvotes: 1

Related Questions