首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >获取Oracle中的第n个工作日期

获取Oracle中的第n个工作日期
EN

Stack Overflow用户
提问于 2016-07-22 01:31:31
回答 3查看 1.1K关注 0票数 0

我们在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;
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38510753

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档