基于Cron表达式的T-SQL函数开发求助：计算下一次执行时间

我之前做定时任务系统的时候刚好实现过类似的T-SQL函数，能处理标准Cron表达式的大部分常用规则（支持*、?、-、,、/、L、#这些通配符），直接给你完整的实现代码：

CREATE FUNCTION dbo.CronNextExecution(
    @cronExpression NVARCHAR(100),
    @inputDate DATETIME
) RETURNS DATETIME
AS
BEGIN
    DECLARE @nextDate DATETIME = DATEADD(SECOND, 1, @inputDate);
    DECLARE @cronParts TABLE (PartIndex INT, PartValue NVARCHAR(50));
    DECLARE @second NVARCHAR(50), @minute NVARCHAR(50), @hour NVARCHAR(50), 
            @day NVARCHAR(50), @month NVARCHAR(50), @weekday NVARCHAR(50);
    DECLARE @is6Field BIT = 0;

    -- 拆分Cron表达式，判断是5字段还是6字段格式
    INSERT INTO @cronParts (PartIndex, PartValue)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS PartIndex, value
    FROM STRING_SPLIT(@cronExpression, ' ')
    WHERE value <> '';

    SELECT @is6Field = CASE WHEN COUNT(*) = 6 THEN 1 ELSE 0 END FROM @cronParts;

    -- 赋值各时间字段
    IF @is6Field = 1
    BEGIN
        SELECT @second = PartValue FROM @cronParts WHERE PartIndex = 1;
        SELECT @minute = PartValue FROM @cronParts WHERE PartIndex = 2;
        SELECT @hour = PartValue FROM @cronParts WHERE PartIndex = 3;
        SELECT @day = PartValue FROM @cronParts WHERE PartIndex = 4;
        SELECT @month = PartValue FROM @cronParts WHERE PartIndex = 5;
        SELECT @weekday = PartValue FROM @cronParts WHERE PartIndex = 6;
    END
    ELSE
    BEGIN
        SET @second = '0'; -- 5字段默认秒为0
        SELECT @minute = PartValue FROM @cronParts WHERE PartIndex = 1;
        SELECT @hour = PartValue FROM @cronParts WHERE PartIndex = 2;
        SELECT @day = PartValue FROM @cronParts WHERE PartIndex = 3;
        SELECT @month = PartValue FROM @cronParts WHERE PartIndex = 4;
        SELECT @weekday = PartValue FROM @cronParts WHERE PartIndex = 5;
    END

    -- 循环查找下一个匹配的时间，最多迭代1000次防止死循环
    DECLARE @iterations INT = 0;
    WHILE @iterations < 1000
    BEGIN
        SET @iterations = @iterations + 1;

        -- 检查月份是否匹配
        IF NOT dbo.IsCronPartMatching(MONTH(@nextDate), @month, 1, 12)
        BEGIN
            SET @nextDate = DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@nextDate), MONTH(@nextDate), 1));
            SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
            CONTINUE;
        END

        -- 检查日期是否匹配（处理?和L）
        DECLARE @maxDay INT = DAY(EOMONTH(@nextDate));
        IF @day = 'L'
        BEGIN
            IF DAY(@nextDate) <> @maxDay
            BEGIN
                SET @nextDate = DATEADD(DAY, @maxDay - DAY(@nextDate), @nextDate);
                SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
                CONTINUE;
            END
        END
        ELSE IF @day <> '?' AND NOT dbo.IsCronPartMatching(DAY(@nextDate), @day, 1, @maxDay)
        BEGIN
            SET @nextDate = DATEADD(DAY, 1, @nextDate);
            SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
            CONTINUE;
        END

        -- 检查星期几是否匹配（处理?、L、#）
        DECLARE @weekDayNum INT = DATEPART(WEEKDAY, @nextDate);
        -- 转换SQL的星期（周日=1）为Cron的星期（周日=0或7，这里统一转成0-6，周日=0）
        SET @weekDayNum = CASE WHEN @weekDayNum = 1 THEN 0 ELSE @weekDayNum - 1 END;

        IF @weekday = 'L'
        BEGIN
            -- 最后一个星期几：计算当月最后一个目标星期几
            DECLARE @lastWeekDay DATETIME = DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@nextDate), MONTH(@nextDate), 1)));
            DECLARE @lastWeekDayNum INT = CASE WHEN DATEPART(WEEKDAY, @lastWeekDay) = 1 THEN 0 ELSE DATEPART(WEEKDAY, @lastWeekDay) - 1 END;
            IF @weekDayNum <> @lastWeekDayNum
            BEGIN
                SET @nextDate = DATEADD(DAY, 1, @nextDate);
                SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
                CONTINUE;
            END
        END
        ELSE IF CHARINDEX('#', @weekday) > 0
        BEGIN
            -- 处理X#Y：第Y个星期X
            DECLARE @targetWeekDay INT = CAST(LEFT(@weekday, CHARINDEX('#', @weekday)-1) AS INT);
            DECLARE @occurrence INT = CAST(RIGHT(@weekday, LEN(@weekday)-CHARINDEX('#', @weekday)) AS INT);
            -- 计算当月第occurrence个targetWeekDay
            DECLARE @firstDayOfMonth DATETIME = DATEFROMPARTS(YEAR(@nextDate), MONTH(@nextDate), 1);
            DECLARE @firstTargetWeekDay DATETIME = DATEADD(DAY, 
                (CASE WHEN DATEPART(WEEKDAY, @firstDayOfMonth) -1 <= @targetWeekDay THEN @targetWeekDay - (DATEPART(WEEKDAY, @firstDayOfMonth)-1) ELSE 7 - ((DATEPART(WEEKDAY, @firstDayOfMonth)-1) - @targetWeekDay) END), 
                @firstDayOfMonth);
            DECLARE @nthTargetWeekDay DATETIME = DATEADD(WEEK, @occurrence-1, @firstTargetWeekDay);
            IF @nextDate <> @nthTargetWeekDay
            BEGIN
                SET @nextDate = DATEADD(DAY, 1, @nextDate);
                SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
                CONTINUE;
            END
        END
        ELSE IF @weekday <> '?' AND NOT dbo.IsCronPartMatching(@weekDayNum, @weekday, 0, 6)
        BEGIN
            SET @nextDate = DATEADD(DAY, 1, @nextDate);
            SET @nextDate = DATEADD(HOUR, 0, DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate)));
            CONTINUE;
        END

        -- 处理日和星期的互斥：如果其中一个是?，另一个生效；如果都不是?，需要同时满足
        IF @day <> '?' AND @weekday <> '?'
        BEGIN
            -- 已经通过上面的检查，继续
        END

        -- 检查小时是否匹配
        IF NOT dbo.IsCronPartMatching(DATEPART(HOUR, @nextDate), @hour, 0, 23)
        BEGIN
            SET @nextDate = DATEADD(HOUR, 1, @nextDate);
            SET @nextDate = DATEADD(MINUTE, 0, DATEADD(SECOND, 0, @nextDate));
            CONTINUE;
        END

        -- 检查分钟是否匹配
        IF NOT dbo.IsCronPartMatching(DATEPART(MINUTE, @nextDate), @minute, 0, 59)
        BEGIN
            SET @nextDate = DATEADD(MINUTE, 1, @nextDate);
            SET @nextDate = DATEADD(SECOND, 0, @nextDate);
            CONTINUE;
        END

        -- 检查秒是否匹配
        IF NOT dbo.IsCronPartMatching(DATEPART(SECOND, @nextDate), @second, 0, 59)
        BEGIN
            SET @nextDate = DATEADD(SECOND, 1, @nextDate);
            CONTINUE;
        END

        -- 所有字段匹配，返回结果
        RETURN @nextDate;
    END

    -- 如果迭代超过1000次还没找到，返回NULL（理论上不会发生）
    RETURN NULL;
END
GO

-- 辅助函数：检查单个Cron字段是否匹配当前值
CREATE FUNCTION dbo.IsCronPartMatching(
    @currentValue INT,
    @cronPart NVARCHAR(50),
    @minValue INT,
    @maxValue INT
) RETURNS BIT
AS
BEGIN
    -- 处理*：匹配所有值
    IF @cronPart = '*'
        RETURN 1;

    -- 处理逗号分隔的多个值
    IF CHARINDEX(',', @cronPart) > 0
    BEGIN
        DECLARE @value NVARCHAR(10);
        DECLARE valueCursor CURSOR FOR
        SELECT value FROM STRING_SPLIT(@cronPart, ',');
        OPEN valueCursor;
        FETCH NEXT FROM valueCursor INTO @value;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF dbo.IsCronPartMatching(@currentValue, @value, @minValue, @maxValue) = 1
            BEGIN
                CLOSE valueCursor;
                DEALLOCATE valueCursor;
                RETURN 1;
            END
            FETCH NEXT FROM valueCursor INTO @value;
        END
        CLOSE valueCursor;
        DEALLOCATE valueCursor;
        RETURN 0;
    END

    -- 处理范围（-）
    IF CHARINDEX('-', @cronPart) > 0
    BEGIN
        DECLARE @start INT = CAST(LEFT(@cronPart, CHARINDEX('-', @cronPart)-1) AS INT);
        DECLARE @end INT = CAST(RIGHT(@cronPart, LEN(@cronPart)-CHARINDEX('-', @cronPart)) AS INT);
        IF @currentValue BETWEEN @start AND @end
            RETURN 1;
        RETURN 0;
    END

    -- 处理步长（/）
    IF CHARINDEX('/', @cronPart) > 0
    BEGIN
        DECLARE @base NVARCHAR(10) = LEFT(@cronPart, CHARINDEX('/', @cronPart)-1);
        DECLARE @step INT = CAST(RIGHT(@cronPart, LEN(@cronPart)-CHARINDEX('/', @cronPart)) AS INT);
        DECLARE @startVal INT = @minValue;
        IF @base <> '*'
            SET @startVal = CAST(@base AS INT);
        IF (@currentValue - @startVal) % @step = 0 AND @currentValue >= @startVal
            RETURN 1;
        RETURN 0;
    END

    -- 处理单个值
    IF CAST(@cronPart AS INT) = @currentValue
        RETURN 1;

    RETURN 0;
END
GO

关键说明： #

• 支持两种Cron格式：6字段（秒 分 时 日 月 周）和5字段（分 时 日 月 周，默认秒为0）
• 支持的通配符规则：
  • *：匹配该字段所有有效值
  • ?：仅用于日和周字段，表示不关心该字段的值（互斥使用，不能同时为?）
  • -：表示范围，比如1-5表示1到5
  • ,：表示多个值，比如1,3,5表示1、3、5
  • /：表示步长，比如*/5表示每5个单位
  • L：用于日字段表示当月最后一天，用于周字段表示当月最后一个指定星期几
  • #：仅用于周字段，比如2#3表示当月第3个星期二（星期几从0=周日开始）

使用示例： #

-- 获取当前时间之后，每天中午12点的下一次执行时间
SELECT dbo.CronNextExecution('0 0 12 * * ?', GETDATE()) AS NextExecutionTime;

-- 获取当前时间之后，每周一早上8点30分的下一次执行时间
SELECT dbo.CronNextExecution('0 30 8 * * 1', GETDATE()) AS NextExecutionTime;

-- 获取当前时间之后，每月最后一天23点59分的下一次执行时间
SELECT dbo.CronNextExecution('0 59 23 L * ?', GETDATE()) AS NextExecutionTime;

-- 获取当前时间之后，每10分钟执行一次的下一次执行时间
SELECT dbo.CronNextExecution('*/10 * * * * ?', GETDATE()) AS NextExecutionTime;

注意事项： #

• SQL Server的DATEPART(WEEKDAY)函数默认周日为1，函数里已经转成Cron标准的0=周日，所以周字段的取值是0-6（0=周日，6=周六）
• 如果Cron表达式格式错误或者没有匹配的时间，函数会在1000次迭代后返回NULL，你可以根据需求调整迭代次数
• 辅助函数IsCronPartMatching是核心，负责单个字段的匹配逻辑，建议不要单独修改它，避免影响主函数的正确性

内容的提问来源于stack exchange，提问作者Muhammad Mutahar Alam