SQL Server 自定義函式 DateAddWorkdays 按工作日加日數

Tiger_Zhao發表於2015-04-03
SQLServer函式

碰到一個需要按工作日向後計算天數的帖子，寫了個算多少個工作日之後的日期函式。

IF OBJECT_ID('dbo.DateAddWorkdays') IS NOT NULL
    DROP FUNCTION DateAddWorkdays
GO

-- 求 @date 之後的第 @workdays 個工作日
CREATE FUNCTION DateAddWorkdays(@workdays int, @date datetime)
RETURNS datetime
AS
BEGIN
    -- 必須呼叫 SET DATEFIRST 1 保證休日判斷的正確
    IF @@DATEFIRST <> 1
        RETURN NULL

    DECLARE @weekday int
    DECLARE @weeks int
    DECLARE @days int
    DECLARE @fulldays int

    SET @weekday = DatePart(weekday, @date) -- 起始日星期幾？
    IF @weekday = 5                         -- 週五多經過2個休日
        SET @fulldays = 2
    ELSE IF @weekday = 6                    -- 週六多經過1個休日
        SET @fulldays = 1
    ELSE
        SET @fulldays = 0                   -- 其它：第二天就是工作日
    
    IF @weekday >= 5                        -- 週五、六、日都要從下週開始第一個工作日
        SET @weekday = 0                    -- 用0表示方便和 @days 相加

    SET @weeks = @workdays / 5              -- 整週數
    SET @days = @workdays % 5               -- 不足一週的工作日數
    SET @fulldays = @fulldays + @weeks * 7  -- 經過整週的天數

    IF (@weekday + @days) < 6               -- 可以結束於週六前
        SET @fulldays = @fulldays + @days
    ELSE                                    -- 否則再多2個休日
        SET @fulldays = @fulldays + @days + 2

    RETURN DateAdd(day, @fulldays, @date)
END
GO


測試程式碼

SET DATEFIRST 1

SELECT dt day0,
       DatePart(weekday,dt) weekday,
       CASE WHEN DatePart(weekday,dt) <=5 THEN
            1
       ELSE
            0
       END workday,
       dbo.DateAddWorkdays(7, dt) day7,
       dbo.DateAddWorkdays(14, dt) day14
  FROM (
        SELECT DateAdd(day,number,'2015-04-01') dt
          FROM master..spt_values
         WHERE type = 'p'
           AND number < 30
       ) t


測試結果

day0           weekday     workday day7            day14
---------- ----------- ----------- ---------- ----------
2015-04-01           3           1 2015-04-10 2015-04-21
2015-04-02           4           1 2015-04-13 2015-04-22
2015-04-03           5           1 2015-04-14 2015-04-23
2015-04-04           6           0 2015-04-14 2015-04-23
2015-04-05           7           0 2015-04-14 2015-04-23
2015-04-06           1           1 2015-04-15 2015-04-24
2015-04-07           2           1 2015-04-16 2015-04-27
2015-04-08           3           1 2015-04-17 2015-04-28
2015-04-09           4           1 2015-04-20 2015-04-29
2015-04-10           5           1 2015-04-21 2015-04-30
2015-04-11           6           0 2015-04-21 2015-04-30
2015-04-12           7           0 2015-04-21 2015-04-30
2015-04-13           1           1 2015-04-22 2015-05-01
2015-04-14           2           1 2015-04-23 2015-05-04
2015-04-15           3           1 2015-04-24 2015-05-05
2015-04-16           4           1 2015-04-27 2015-05-06
2015-04-17           5           1 2015-04-28 2015-05-07
2015-04-18           6           0 2015-04-28 2015-05-07
2015-04-19           7           0 2015-04-28 2015-05-07
2015-04-20           1           1 2015-04-29 2015-05-08
2015-04-21           2           1 2015-04-30 2015-05-11
2015-04-22           3           1 2015-05-01 2015-05-12
2015-04-23           4           1 2015-05-04 2015-05-13
2015-04-24           5           1 2015-05-05 2015-05-14
2015-04-25           6           0 2015-05-05 2015-05-14
2015-04-26           7           0 2015-05-05 2015-05-14
2015-04-27           1           1 2015-05-06 2015-05-15
2015-04-28           2           1 2015-05-07 2015-05-18
2015-04-29           3           1 2015-05-08 2015-05-19
2015-04-30           4           1 2015-05-11 2015-05-20


 

相關文章