| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Date Function.
I have not tested just found on MetaLink
Rick
SET ECHO off
REM NAME:
TFSBSDAY.SQL
REM
USAGE:"@path/tfsbsday"
REM
------------------------------------------------------------------------
REM REQUIREMENTS:
REM CREATE
PROCEDURE
REM
------------------------------------------------------------------------
REM PURPOSE:
REM The function
created by this script will reutrn the number of
REM business
days between two dates.
REM
------------------------------------------------------------------------
REM EXAMPLE:
REM SQL>
@dates.sql
REM
REM Function
created.
REM
REM SQL> select
num_Business_Days('20-jun-95','28-jun-95') "Business
REM Days" from
dual;
REM
REM Business
Days
REM
REM
-------------
REM
5
REM
------------------------------------------------------------------------
REM DISCLAIMER:
REM This script
is provided for educational purposes only. It is NOT
REM supported by
Oracle World Wide Technical Support.
REM The script
has been tested and appears to work as intended.
REM You should
always run new scripts on a test instance initially.
REM
------------------------------------------------------------------------
REM Main text of
script follows:
create or replace
function
num_Business_Days(start_date in date, end_date in date)
return number is
currdate
date := start_date; /* holds the next date */
theDay
varchar2(10);/* day of the week for currdate */
countBusiness
number := 0; /* counter for business days */
begin
/* start date must
be earlier than end date */
if end_date -
start_date <= 0 then
return (0);
end if;
loop
/* go to the
next day */
currdate :=
to_date(currdate+1);
/* finished if
end_date is reached */
exit when
currdate = end_date;
/* what day of
the week is it? */
select
to_char(currdate,'fmDay') into theDay from dual;
/* count it only
if it is a weekday */
if theDay <>
'Saturday' and theDay <> 'Sunday' then
countBusiness
:= countBusiness + 1;
end if;
end loop;
return
(countBusiness);
end;
/
Sridhar Moparthy
<sridhar.moparthy To: Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>
@wcom.com> cc:
Sent by: Subject: Date Function.
root_at_fatcity.com
01/28/2002 03:25
PM
Please respond to
ORACLE-L
Hi All,
Is there any function in Oracle8i ( or in PHP4.X) to calculate number of Business days ( excluding Saturday and Sunday) between two given dates?
Thanks in advance.
Sridhar.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sridhar Moparthy INET: sridhar.moparthy_at_wcom.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rick_Cale_at_teamhealth.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jan 28 2002 - 15:03:30 CST
 |
 |