How to count date difference excluding weekend and holidays in MySQL

by

You might want to try this:

  1. Count the number of working days (took it from here)

    SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)

    This gives you 261 working days for 2012.

  2. Now you need to know your holidays that are not on a weekend

    SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6

    The result of this depends on your holiday table.

  3. We need to get that in one query:

    SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)

    This should be it.

Edit: Please be aware that this only works properly if your end date is higher than your start date.

More Related Contents:

  1. When using with SAVEPOINT and ROLLBACK , what will be the output of this?
  2. How do I make this MySQL query faster and better?
  3. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  4. Can you use an alias in the WHERE clause in mysql?
  5. Select values that meet different conditions on different rows?
  6. How to find similar results and sort by similarity?
  7. MySQL error: key specification without a key length
  8. Insert into multiple tables in one query
  9. generate_series() equivalent in MySQL
  10. Select row with most recent date per user
  11. MySQL : isn’t in GROUP BY
  12. MySQL: Can’t create table (errno: 150)
  13. Mysql query to dynamically convert rows to columns
  14. Why does MySQL report a syntax error on FULL OUTER JOIN?
  15. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  16. Mixing ANSI 1992 JOINs and COMMAs in a query
  17. SQL: Creating a Relational table with 2 different auto_increment
  18. How do I add a custom CHECK constraint on a MySQL table?
  19. MySQL variable format for a “NOT IN” list of values
  20. Convert partially non-numeric text into number in MySQL query
  21. Order by COUNT per value
  22. MySQL Select last 7 days
  23. How to select the most recent set of dated records from a mysql table
  24. Second SELECT query if first SELECT returns 0 rows
  25. Set value to NULL in MySQL
  26. Difference between two table structure
  27. How to retrieve JSON data from MySQL?
  28. When to use a left outer join?
  29. How to select non “unique” rows
  30. What are the benefits of only_full_group_by mode?

Leave a Comment