Calculate difference between 2 dates in SQL, excluding weekend days
date, sql
Solution
You should try with a 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 |
Problem
I would like to build an SQL query which calculates the difference between 2 dates, without counting the week-end days in the result. Is there any way to format the dates to obtain this result ? For example for Oracle database : ``` select sysdate - creation_dttm from the_table ```