MSSQL 週一到週五下午五點半過後與假日的規則

DECLARE @SelectDate DateTime = '2017/12/29 17:00';

DECLARE @StartDateTime Datetime = CONVERT(VARCHAR(10), @SelectDate, 111) + ' 17:30:00',

-- 2017-12-11 17:30:00.000 (今天是 2017/12/29 17:00)

@EndDateTime DateTime = CONVERT(VARCHAR(10), @SelectDate + 1, 111);

-- 2017-12-12 00:00:00.000 (今天是 2017/12/29 17:00)

DECLARE @IsFitRull BIT = 1; -- 預設不符合規則

IF (

DATEPART(DW, @SelectDate) IN (1, 7) OR -- 星期六或星期日

(DATEPART(DW, @SelectDate) IN (2, 3, 4, 5, 6) AND

@StartDateTime <= @SelectDate AND @SelectDate < @EndDateTime) -- 星期一 ~ 星期五 17:30 ~ 隔天 00:00

)

BEGIN

SET @IsFitRull = 0;

END

SELECT @IsFitRull