获取Oracle中的第n个工作日期
我们在SQL中已经有一个函数，当您传递Startdate、Enddate和Workday( nth )时，它可以计算第n个工作日期(不包括周末和节假日)。它给你第n个工作日...
工作SQL函数：
FUNCTION [dbo].[getNthWorkingDate]
(
@StartDate as datetime,
@EndDate as datetime,
@WorkDay as int
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @WorkDate as datetime, @LeaveYear as smallint,@iCount as int
set @LeaveYear = datepart(year, @StartDate)
set @iCount = 1
WHILE (@StartDate < @EndDate)
BEGIN
IF (DATENAME(WEEKDAY,@StartDate ) = 'SUNDAY') OR (DATENAME(WEEKDAY,@StartDate ) = 'SATURDAY')
-- Just to keep the if statement with out code
set @iCount = @iCount;
ELSE IF EXISTS (SELECT * FROM HOLIDAYS WHERE CAST (HOLIDAY + ' ' + CAST(@LeaveYear AS VARCHAR) AS DATETIME) = @StartDate)
-- Just to keep the if statement with out code
set @iCount = @iCount;
ELSE
begin
set @WorkDate = @StartDate
if @iCount = @WorkDay
BREAK;
else
set @iCount = @iCount + 1;
end
set @StartDate = dateadd(day, 1, @StartDate );
END
-- Return the result of the function
RETURN @WorkDate
END我正在尝试在oracle中重新创建这个函数(oracle的新功能)，我做了一些更改，但无法让它工作，我想我在循环中遗漏了一些东西，任何帮助都将不胜感激…提前谢谢..
ORACLE函数：
create or replace FUNCTION GETNTHWORKINGDATE (pStartDate DATE,
pEndDate DATE,
pWorkDay NUMBER)
RETURN DATE
AS
vStartDate DATE;
vWorkDate DATE ;
vCount NUMBER;
vHoliday DATE;
BEGIN
vCount := 1;
BEGIN
SELECT HOLIDAY_DATE INTO vHoliday FROM HOLIDAY WHERE (to_char(HOLIDAY_DATE, 'MM DD') = to_char(pStartDate, 'MM DD'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
vHoliday := NULL;
END;
vStartDate := pStartDate;
BEGIN
WHILE (vStartDate < pEndDate)
LOOP
IF (to_char(vStartDate, 'DAY') = 'SUNDAY' ) OR (to_char(vStartDate, 'DAY') = 'SATURDAY') THEN
vCount := vCount;
ELSIF (to_char(vHoliday, 'MM DD') = to_char(vStartDate, 'MM DD')) THEN
vCount := vCount;
ELSE
vWorkDate := vStartDate;
IF vCount = pWorkDay THEN
EXIT;
ELSE
vCount := vCount + 1;
END IF;
END IF;
vStartDate := vStartDate + 1;
END LOOP;
END;
RETURN vWorkDate;
END GETNTHWORKINGDATE;回答 3
Stack Overflow用户
发布于 2016-07-27 03:18:29
create or replace FUNCTION GETNTHWORKINGDATE (pStartDate DATE,
pEndDate DATE,
pWorkDay NUMBER)
RETURN DATE AS
vStartDate DATE;
vWorkDate DATE ;
vCount NUMBER;
vHoliday DATE;
BEGIN
vCount := 1;
vStartDate := pStartDate;
BEGIN
WHILE (vStartDate < pEndDate)
LOOP
BEGIN
--Select Holiday Month and Date into vHoliday Variable when Start Date is a holiday.
SELECT HOLIDAY_DATE INTO vHoliday FROM HOLIDAY WHERE (to_char(HOLIDAY_DATE, 'MM DD') = to_char(vStartDate, 'MM DD'));
EXCEPTION
WHEN NO_DATA_FOUND THEN vHoliday := NULL;
END;
-- Code to eliminate Weekends
IF (to_char(vStartDate, 'D') = 1 ) OR (to_char(vStartDate, 'D') = 7) THEN
vCount := vCount;
--Code to eliminate Holiday's from holiday table.
ELSIF (to_char(vHoliday, 'MM DD') = to_char(vStartDate, 'MM DD')) THEN
vCount := vCount;
ELSE
vWorkDate := vStartDate;
IF vCount = pWorkDay THEN
EXIT;
ELSE
vCount := vCount + 1;
END IF;
END IF;
vStartDate := vStartDate + 1;
END LOOP;
END;
RETURN vWorkDate;
END GETNTHWORKINGDATE;Stack Overflow用户
发布于 2016-07-22 03:06:35
在普通的SQL中，类似这样的东西是有效的。如果您真的不需要使用PL/SQL，那么最好不要使用它。如果您确实需要使用它，请根据需要进行调整。我在评论中看到您已经更改了需求；请根据需要进行调整。
2 * :wd_number + 5的“神奇数字”是为了确保我们添加足够的日历日期，使其至少包括:wd_number工作日；+5用于:wd_number的低值。这不是最有效的解决方案，但它不会浪费超过几毫秒，所以我没有费心让它更有效。
with holidays( holiday_date, holiday_name ) as (
select date '2016-01-01', 'New Year''s Day' from dual union all
select date '2016-04-01', 'April Fools'' Day' from dual union all
select date '2016-05-01', 'May First' from dual
),
work_days ( dt ) as (
select to_date(:start_date, 'yyyy-mm-dd') + level - 1
from dual
where to_char(to_date(:start_date, 'yyyy-mm-dd') + level - 1, 'Dy')
not in ('Sat', 'Sun')
connect by level < 2 * to_number(:wd_number) + 5
minus
select holiday_date
from holidays
),
ordered_work_days ( dt, rn ) as (
select dt, row_number() over (order by dt)
from work_days
)
select dt
from ordered_work_days
where rn = to_number(:wd_number)
;Stack Overflow用户
发布于 2020-10-05 06:14:18
-排除周末并计算第N个工作日SELECT --CURRENTDATE，WORK_DATE，WORK_DAY，ROW_NUMBER() OVER (ORDER by WORK_DATE) NTH_WORKING_DAY FROM (-按相应日期获取所有日期SELECT CURRENTDATE，FIRST_DATE + (level - 1) AS WORK_DATE，TO_CHAR(FIRST_DATE + (LEVEL - 1)，' Day ') AS WORK_DAY FROM (-获取当月的当前日期，第一个日期和最后一个日期SELECT sysdate CURRENTDATE，TRUNC(sysdate，‘'MM') AS FIRST_DATE，LAST_DAY(sysdate) LAST_DATE FROM DUAL ) CONNECT BY FIRST_DATE + (level - 1) <= LAST_DATE ) WHERE TRIM(WORK_DAY) NOT IN (’星期六‘，’星期日‘)；
https://stackoverflow.com/questions/38510753
复制相似问题
腾讯云开发者
