| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ??? Number of days betwwen two dates?
Joseph,
Here it comes:
The following SQL script shows an algorythm that uses the standard Oracle date functions to calculate the number of business days between to dates. This method cannot exclude holidays (obviously), however there are many times that just the standard business days is useful. I am sure there are other algorythms that could be used, however this one has worked for me. The algorythm is described below in the comments of the script.
/*****************************************************************/
/* An example of business days calculation in SQL
*/
/*
*/
/* The algorythm is:
*/
/*
*/
/* 1) Take the absolute difference between the dates
*/
/* to_date('&todate') - to_date('&frdate')
*/
/* 2) Subtract the weekends (number of weeks in the range
*/
/* TRUNC(to_date('&todate'),'D') = 1st day of week that
*/
/* end of period is in
*/
/* TRUNC(to_date('&frdate'),'D') = Last day of week that
*/
/* start of period is in
*/
/* So subtracting these two gives the number of days
*/
/* between the two dates but including all of the days in
*/
/* the weeks that the dates start and end in. When this
*/
/* number is divided by 7 it gives the number of weeks.
*/
/* Multiplying by 2 gives the number of weekend days.
*/
/* 3) Subtract 1 day if the ending date is on a saturday
*/
/* DECODE(to_char(to_date('&todate'),'D'),7,-1,0)
*/
/* --> If the day of the week is saturday (7), returns -1
*/
/* 4) Subtract 1 day if the start date is on a sunday
*/
/* DECODE(to_char(to_date('&frdate'),'D'),1,-1)
*/
/* --> If the day of the week is sunday (1), returns 1
*/
/* 5) Add one day to make the range inclusive (The '1 + ' )
/*---------------------------------------------------------------*/
/* Author: Kenneth Atkins (katkins_at_olywa.net)
*/
/* http://www.olywa.net/katkins/oratip
/*****************************************************************/
define frdate = '&1'
define todate = '&2'
set verify off
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)
+ DECODE(to_char(to_date('&frdate'),'D'),1,-1,0)
Business_Days
from dual
/
Here is an example of running the script:
SQL> @busdays 01-AUG-96 15-AUG-96
FROM_DATE TO_DATE BUSINESS_DAYS
01-AUG-96 15-AUG-96 11 1 row selected.
This same algorythm can also be put into a stored function:
CREATE OR REPLACE FUNCTION business_days(p_from_date IN DATE, p_to_date IN DATE)
RETURN NUMBER IS
busdays NUMBER;
BEGIN
/*****************************************************************/
/* BUSINESS_DAYS - Database Function to Calculate number of
*/
/* business days between two dates
/*---------------------------------------------------------------*/
/* Author: Kenneth Atkins (katkins_at_olywa.net)
*/
/* http://www.olywa.net/katkins/oratip
/*****************************************************************/
-- Get the absolute date range
busdays := TRUNC(p_to_date) - TRUNC(p_from_date)
-- 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(p_to_date,'D')-TRUNC(to_date(p_from_date),'D'))/7)*2
-- Add one to make the range inclusive
+ 1;
/* Adjust for ending date on a saturday */
IF TO_CHAR(p_to_date,'D') = '7' THEN
busdays := busdays - 1;
END IF;
/* Adjust for starting date on a sunday */
IF TO_CHAR(p_from_date,'D') = '1' THEN
busdays := busdays - 1;
END IF;
RETURN(busdays);
END;
/
show errors;
Here is an example of calling this database function:
SQL> select business_days('01-AUG-96','15-AUG-96') from dual;
BUSINESS_DAYS('01-AUG-96','15-AUG-96')
11
1 row selected.
Of course in a stored function, you could add code to substract holidays also. Perhaps something like:
SELECT COUNT(*) INTO nHolidays
FROM Holiday_Table
WHERE holiday_date BETWEEN p_from_date AND p_to_date;
Then substract nHolidays from your business_days variable before returning.
Mario
On Wed, 30 Jul 1997 09:23:52 -0700, "Huang, Joseph" <huang_at_baptized.com.---> wrote:
> I need a function, which I can use in other stored procedures, to return number of days of two given dates. The only similar function I can found in Oracle manual is MONTHS_BETWEEN(). However, the problem is MONTHs_BETWEEN() return number based on 31-day month which is incorrect normally. For example, instead of 29 days, the function returns 1.0322581 (32 days) from 02/01/97 to 03/02/97. > >Before writing our own function to get correct number of days, I'd like to know whether this kind of function exists somewhere so I don't need to redo it. Any suggestions? > >Thanks in advance. > >Joseph Huang > > >-- >My email address is intentionally invalid to foil spammers. Delete the ".---" to get my real address. >Received on Fri Aug 01 1997 - 00:00:00 CDT
 |
 |