我们在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;
https://stackoverflow.com/questions/38510753
复制相似问题