# 取得日期區間內工作日
###### tags: `SQL` `ORACLE` `MSSQL`

mssql

USE [LCMS]
GO
/****** Object:  UserDefinedFunction [dbo].[F_WORKING_DAY]    Script Date: 2020/6/3 上午 10:16:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*取得日期區間內預設工作日*/
ALTER FUNCTION [dbo].[F_WORKING_DAY]
(
 @StartDate DATE,
 @EndDate DATE
)
RETURNS INT
AS
BEGIN
IF @StartDate IS NULL  RETURN NULL
IF @EndDate IS NULL RETURN NULL
IF @StartDate > @EndDate RETURN NULL

 RETURN
  (DATEDIFF(DAY, @StartDate, @EndDate) + 1)  
  -(DATEDIFF(WEEK, @StartDate, @EndDate) * 2)  
  -(CASE WHEN DATEPART(WEEKDAY, @StartDate) in (1,7) THEN 1 ELSE 0 END)
END

# oracle

CREATE OR REPLACE FUNCTION LCMS.F_WORKING_DAY(P_START_DT DATE, P_END_DT DATE) RETURN NUMBER AS 
v_cnt number;
v_start_dt date;
v_end_dt date;
BEGIN
    
    IF P_START_DT IS NULL OR P_END_DT IS NULL THEN
        return null;
    END IF;
    
    IF P_START_DT > P_END_DT THEN
        return null;
    END IF;
    
    v_end_dt := trunc(P_END_DT);
    v_start_dt := trunc(P_START_DT);
    
    select count(1) into v_cnt
    from (
        select (v_end_dt-level+1) dt
        from dual
        connect by level <= (trunc(v_end_dt - v_start_dt)+1)
    )
    where dt >= v_start_dt
    and to_char(dt,'D') not in(1,7);
    
    return v_cnt;
    
END F_WORKING_DAY;
/