Count days between two dates, excluding weekends (MySQL only)

by

Simply try it using a simple function :

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Test : 

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Result : 

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

More Related Contents:

  1. How to get difference between two dates in months using MySQL query?
  2. Left Outer Join doesn’t return all rows from my left table?
  3. How to populate a table with a range of dates?
  4. How to convert a string to date in MySQL?
  5. MySQL Query GROUP BY day / month / year
  6. Converting a date in MySQL from string field
  7. MySQL: Select All Dates In a Range Even If No Records Present
  8. Mysql: Select all data between two dates
  9. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  10. MySQL Query – Records between Today and Last 30 Days
  11. What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?
  12. CURRENT_DATE/CURDATE() not working as default DATE value
  13. Select mysql query between date?
  14. How to select date from datetime column?
  15. Get the latest date from grouped MySQL data
  16. mysql: get record count between two date-time
  17. How to get first day of every corresponding month in mysql?
  18. How to select the most recent set of dated records from a mysql table
  19. How to cast DATETIME as a DATE in mysql?
  20. How do I get the first day of the week of a date in mysql?
  21. MySQL date format
  22. MySQL: How to select records for this week?
  23. Select all months within given date span, including the ones with 0 values
  24. Update date + one year in mysql
  25. mysql date comparison with date_format
  26. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  27. MySQL cumulative sum grouped by date
  28. MySQL: Find Missing Dates Between a Date Range
  29. How do I extract Month and Year in a MySQL date and compare them?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment