# Thread: Challenge, Oracle x MSSQL

1. Registered User
Join Date
Dec 2002
Location
Czech Republic
Posts
249

## Unanswered: Challenge, Oracle x MSSQL

I am looking for a query to calculate a number of bussiness days between two dates without loops or special huge tables.

http://dbforums.com/t692935.html

The first solution needs a table with thousands of records, the second one hundereds of loops per one record.

What is is the best way how to solve it in ORACLE?
Are you able to solve it in a real SQL code, i.e. in one select like in MSSQL?

Code:
```/* MSSQL code */
declare @now datetime set @now='20030602'
select
cast(date as datetime) as "From"
,@now                  as "To"
,5*((datediff(day,
)+1)/7)
-case                ((datepart(dw,date)+@@DATEFIRST-2)%7+1)
when 1 then 0
when 2 then 1
when 3 then 2
when 4 then 3
when 5 then 4
when 6 then 5
when 7 then 5 end
-case                ((datepart(dw,@now)+@@DATEFIRST-2)%7+1)
when 1 then 4
when 2 then 3
when 3 then 2
when 4 then 1
when 5 then 0
when 6 then 0
when 7 then 0 end as "Bussiness days"
from
(
select '20030502' as date
union all select '20030503'
union all select '20030504'
union all select '20030505'
union all select '20030506'
union all select '20030507'
union all select '20030508'
union all select '20030509'
) as x

/*
a derived table is used
date<=@now

((datepart(dw,date)+@@DATEFIRST-2)%7) is a deterministic version of ((datepart(dw,date)-1)
( MO=0,TU=1,...,SA=5,SU=6 )

The algorithm is very easy. I round dates to limits of weeks, calculate a difference and I deduct not affected bussiness days
from both the first week and last one.

'20030505'<-('20030509','20030603')->'20030608'
'20030505'<-     5 weeks=25 BD     ->'20030608'
('20030505','20030509') ('20030603','20030608')
diff 4 BD                       diff 3 BD

R=25-4-3= 18 BD
==========================================
*/```
Best regards, Ivo Spaleny.

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: Challenge, Oracle x MSSQL

Originally posted by ispaleny
I am looking for a query to calculate a number of bussiness days between two dates without loops or special huge tables.

http://dbforums.com/t692935.html

The first solution needs a table with thousands of records, the second one hundereds of loops per one record.
If you follow your second link above through you will find a solution I posted that uses neither large tables nor loops with 100s of iterations.

I'll post it again here anyway (probably more up to date).

Code:
```CREATE OR REPLACE PACKAGE bdays_pkg IS

/*
|| Package for calculating with "business days", i.e. counting only weekdays (Mon-Fri)
*/

FUNCTION bdays( p_d1 DATE, p_d2 DATE ) RETURN NUMBER;
/*
|| Returns number of business days between p_d1 and p_d2
|| Note: this figure is EXCLUSIVE, i.e. bdays( sysdate-1, sysdate ) = 1
*/

END;
/

CREATE OR REPLACE PACKAGE BODY bdays_pkg IS

/*
|| These constants are defined in a way that is independent of how days are numbered
|| in the particular database (since this can vary from country to country).
|| Of course, it does assume that Saturdays and Sundays are the weekend days!
*/
k_weekend_day1 CONSTANT INTEGER := TO_CHAR( TO_DATE('04/01/2003','DD/MM/YYYY'),'D');
k_weekend_day2 CONSTANT INTEGER := TO_CHAR( TO_DATE('05/01/2003','DD/MM/YYYY'),'D');

FUNCTION bdays( p_d1 DATE, p_d2 DATE ) RETURN NUMBER
IS
v_d1             DATE    := TRUNC( LEAST(p_d1,p_d2) );        /* Ignore time portion */
v_d2             DATE    := TRUNC( GREATEST(p_d1,p_d2) );     /* Ignore time portion */
v_num_full_weeks INTEGER := TRUNC((v_d2-v_d1)/7);             /* Number of full 7-day weeks in period */
v_num_odd_days   INTEGER := (v_d2-v_d1) - v_num_full_weeks*7; /* Number of odd days */
v_bdays          INTEGER := v_num_full_weeks*5;               /* Number of business days
- initially ignoring odd days */
BEGIN
/* Account for odd days */
FOR i IN 1..v_num_odd_days LOOP
IF TO_CHAR( v_d1-1+i, 'D') NOT IN (k_weekend_day1,k_weekend_day2) THEN
v_bdays := v_bdays+1;
END IF;
END LOOP;
IF p_d1 > p_d2 THEN
v_bdays := -v_bdays;
END IF;
RETURN v_bdays;
END;

END;
/```
The loop here will never iterate more than 4 times.

I also have a "pure SQL" solution that looks like this:

Code:
```SELECT
FLOOR( ABS(TRUNC(date2)-TRUNC(date1))/7)*5
+ NVL(LENGTH(TRANSLATE( SUBSTR( '1234567123456'
, TO_NUMBER(TO_CHAR(TRUNC( LEAST(date1,date2) ),'D'))
, MOD(FLOOR(ABS(TRUNC(date1)-TRUNC(date2))),7)
)
, '671'
, '6'
)
)
,0
FROM tablename;```

3. Registered User
Join Date
May 2003
Location
France
Posts
112
hello,

these query return number of business days beetween two date without loops :

select
'&frdate' From_Date
,'&todate' To_Date,
1 + to_date('&todate') - to_date('&frdate') -
((TRUNC(to_date('&todate'),'D') - TRUNC(to_date('&frdate'),'D'))/7)*2
+ DECODE(to_char(to_date('&todate'),'D'),7,-1,0)
from dual

Hope this will help

4. Registered User
Join Date
Apr 2002
Location
California, USA
Posts
482
You can also use these code snippets to calculate the number of business days between two dates:

Hope that helps,

clio_usa - OCP - DBA

dbaclick.com

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•