在oracle sql中为日期差异创建自定义函数(周末和节假日除外)
我需要使用Oracle中的自定义功能,将两个日期之间的天数计算为十进制,不包括周末和假期 SQL. 网站上也有类似的问题.但是,正如我所看到的,它们都没有要求使用自定义函数将其输出为十进制数.我之所以需要一个小数,是为了以后可以使用/提取时间分量.如果已经有这样的问题,请分享链接.
I need to calculate the number of days between two dates as decimal, excluding the weekends and holidays by using a custom function in Oracle SQL. There are similar questions on the site; however as I could see, none of them asks for an output as decimal using a custom function. The reason why I need a decimal is to be able to use/extract time component afterwards. If there is already a question like this, please just share the link.
Tried to write a function as below with the help of the additional content I found on the internet thanks to the author. The inner subqueries works fine seperately, but it doesn't work as a whole function.
总而言之,这个想法是:
In brief, the idea is:
(计算开始日期和结束日期之间的日差)->(不包括开始日期和结束日期之间的周末天数)->(不包括开始日期和结束日期之间的周末天数)
当我尝试保存该函数时,出现错误PLS-00103: Encountered the symbol "end-of-file"
.由于我已经是函数的新手,可能会缺少一些基本知识.
When I try to save the function, it gives the error PLS-00103: Encountered the symbol "end-of-file"
. Since I am already new in functions maybe missing something basic.
最后,如果您对如何提高代码效率提出了建议,也请告诉我.
Lastly, also please let me know if you have suggestions on how to make the code more efficient.
提前谢谢!
CREATE OR REPLACE FUNCTION NET_WORKING_DAYS (startdate IN DATE, enddate IN DATE)
RETURN NUMBER IS
WORKINGDAYS_BETWEEN NUMBER;
BEGIN
SELECT
-- number of days between startdate and enddate
(
SELECT (TO_DATE('20160831150000','YYYYMMDDHH24MISS') - TO_DATE('20160801000000','YYYYMMDDHH24MISS') ) DAYS_BETWEEN
FROM DUAL
)
-
-- number of weekend days (after a given date)
(
SELECT COUNT(1) WEEKEND_DAYS_BETWEEN
FROM
(
SELECT
TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ as day_date, --2016/07/01 is a constant/given date for this formula
TO_CHAR(TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ , 'D') day_of_week
FROM
(
SELECT ROWNUM-1 SEQ
FROM ( SELECT 1 FROM DUAL CONNECT BY LEVEL<= 365 * 5) --5 years
)
ORDER BY 1
)
WHERE day_of_week IN (6,7)
AND day_date > TO_DATE('20160801000000','YYYYMMDDHH24MISS') --this should be replaced with startdate parameter
AND day_date < TO_DATE('20160831000000','YYYYMMDDHH24MISS') --this should be replaced with enddate parameter
)
-
-- number of holidays (after a given date)
(
SELECT COUNT(1)
FROM HOLIDAYS
WHERE HOLIDAY_DATE > TO_DATE('20160801000000','YYYYMMDDHH24MISS') --this should be replaced with startdate parameter
AND HOLIDAY_DATE < TO_DATE('20160831000000','YYYYMMDDHH24MISS') --this should be replaced with enddate parameter
)
INTO WORKINGDAYS_BETWEEN
FROM DUAL;
RETURN WORKINGDAYS_BETWEEN;
END NET_WORKING_DAYS;
**编辑-1:假期已经在数据库的HOLIDAYS表中定义,并且该日期范围是20160801000000
到20160831000000
,30.06.2016
是假期日期.
**EDIT-1: Holidays are already defined in HOLIDAYS table in the database and for this date range from 20160801000000
to 20160831000000
, 30.06.2016
is the holiday date.
您不需要使用行生成器来枚举每天以获取工作日数-只需使用简单的计算即可完成:
You do not need to use a row generator to enumerate every day to get the number of week days - it can be done using a simple calculation:
从在这里我的答案:
CREATE FUNCTION getWorkingDays (
in_start_date IN DATE,
in_end_date IN DATE
) RETURN NUMBER DETERMINISTIC
IS
p_start_date DATE;
p_end_date DATE;
p_working_days NUMBER;
p_holiday_days NUMBER;
BEGIN
IF in_start_date IS NULL OR in_end_date IS NULL THEN
RETURN NUll;
END IF;
p_start_date := LEAST( in_start_date, in_end_date );
p_end_date := GREATEST( in_start_date, in_end_date );
-- 5/7 * ( Number of days between monday of the week containing the start date
-- and monday of the week containing the end date )
-- + LEAST( day of week for end date, 5 )
-- - LEAST( day of week for start date, 5 )
p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
+ LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
- LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );
SELECT COALESCE(
SUM(
LEAST( p_end_date, holiday_date + INTERVAL '1' DAY )
- GREATEST( p_start_date, holiday_date )
),
0
)
INTO p_holiday_days
FROM Holidays
WHERE HOLIDAY_DATE BETWEEN TRUNC( p_start_date )
AND TRUNC( p_end_date )
AND HOLIDAY_DATE - TRUNC( HOLIDAY_DATE, 'IW' ) < 5;
RETURN GREATEST( p_working_days - p_holiday_days, 0 );
END;
/