Oracle SQL: Calculate the amount of workdays (Mon-Fri) between two dates

I searched the net for a problem in finding a way to calculate the workdays between two date values. After I tested a couple of solutions I focused to one where I didn’t necessarily need a extra select to solve that issue. I found a post at asktom.oracle.com The described function itself looked like that: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 -- Created by Sonali Kelkar from Newton, MA USA CREATE OR REPLACE FUNCTION num_business_days(start_date IN DATE, end_date IN DATE) RETURN NUMBER IS busdays NUMBER := 0; stDate DATE; enDate DATE; BEGIN stDate := TRUNC(start_date); enDate := TRUNC(end_date); if enDate >= stDate then -- Get the absolute date range busdays := enDate - stDate -- Now subtract the weekends -- this statement rounds the range to whole weeks (using -- TRUNC and determines the number of days in the range. -- then it divides by 7 to get the number of weeks, and -- multiplies by 2 to get the number of weekend days. - ((TRUNC(enDate,'D')-TRUNC(stDate,'D'))/7)*2 -- Add one to make the range inclusive + 1; /* Adjust for ending date on a saturday */ IF TO_CHAR(enDate,'D') = '7' THEN busdays := busdays - 1; END IF; /* Adjust for starting date on a sunday */ IF TO_CHAR(stDate,'D') = '1' THEN busdays := busdays - 1; END IF; else busdays := 0; END IF; RETURN(busdays); END; / I did had some issues with the TO_CHAR(stDate,'D') logic and my German character set. Because of this I looked further and found a solution by Frank Kulash at the Oracle forum: 1 2 1 + TRUNC (dt) - TRUNC (dt, 'IW') Finally I was able creating a logic which I could use in my select. To make it more readable for you I created a from dual select: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 select (end_date-start_date) - (((TRUNC(end_date,'D')-TRUNC(start_date,'D'))/7)*2) + 1 - case when (1 + TRUNC (end_date) - TRUNC (end_date, 'IW')) = '6' then 1 else 0 end - case when (1 + TRUNC (end_date) - TRUNC (end_date, 'IW')) = '7' then 2 else 0 end + case when (1 + TRUNC (start_date) - TRUNC (start_date, 'IW')) = '7' then 1 else 0 end as amount_of_workdays from ( select to_date('06.03.2015','dd.mm.yyyy') as start_date, to_date('07.03.2015','dd.mm.yyyy') as end_date from dual )