Crontab

This article provides an implementation of a crontab file's time/date specification expression in T-SQL. NCrontab is a great (and better!) choice for this if you can use a .NET library, but if you can't do so inside SQL Server (maybe your DBAs don't like CLR functions, maybe you're in Azure) then this may be of use.

It has two component functions:

  • tvf_GetNextOccurrence takes a crontab expression and returns a corresponding list of DATETIME values;
  • tvf_CrontabFieldValues is a helper function that parses individual date/time component fields into corresponding value lists.

tvf_GetNextOccurrence

Returns a list of DATETIME values corresponding to a crontab expression. Parameters:

  • @after – lower bound for values returned;
  • @crontabExpr – the crontab expression;
  • @limit – the maximum number of occurrences to return.

Passing these parameters into tvf_GetNextOccurrence returns a list of the first @limit dates & times indicated by @crontabExpr after @after. 

/*
 * Return the next few start date/times indicated by a crontab expression
 * (c) Richard Swinbank 2019-2020
 * http://richardswinbank.net
 */
CREATE FUNCTION [dbo].[tvf_GetNextOccurrence] (
  @after DATETIME
, @crontabExpr VARCHAR(255)
, @limit INT = 1
) RETURNS TABLE
AS RETURN
 
WITH fields AS (
  -- split incoming expression into its five fields
  SELECT
    SUBSTRING(
      @crontabExpr
    , LAG(N, 1, 0) OVER (ORDER BY N) + 1
    , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
    ) AS FieldValue
  , CASE ROW_NUMBER() OVER (ORDER BY N)
      WHEN 1 THEN 'MINUTES'
      WHEN 2 THEN 'HOURS'
      WHEN 3 THEN 'DAYS'
      WHEN 4 THEN 'MONTHS'
      WHEN 5 THEN 'DAYS-OF-WEEK'
    END AS FieldName
  FROM dbo.TinyIntegers n
  WHERE SUBSTRING(REPLACE(@crontabExpr, ' ', 'x') + 'x', N, 1) = 'x'  
    -- really we're looking for spaces, but turn them into 'x' because T-SQL doesn't always play nicely with them
  AND LEN(@crontabExpr) - LEN(REPLACE(@crontabExpr, ' ', '')) = 4  -- refuse to handle badly-formatted expressions
), [values] AS (
  -- expand the list of fields into a list of corresponding field values
  SELECT
    f.FieldName
  , tvf.N AS [Value]
  FROM fields f
    CROSS APPLY tvf_CrontabFieldValues (FieldValue, FieldName) tvf
)
SELECT TOP (@limit)
  validated.[ScheduledDateTime] AS NextOccurrence
FROM (
  SELECT [Value] AS [Minute]  -- list of minutes expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'MINUTES'
) m CROSS JOIN (
  SELECT [Value] AS [Hour]  -- list of hours expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'HOURS'
) h CROSS JOIN (
  SELECT [Value] AS [DayOfMonth]  -- list of days-of-month expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'DAYS'
) d CROSS JOIN (
  SELECT [Value] AS [Month]  -- list of months expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'MONTHS'
) mth CROSS JOIN (
  SELECT [Value] AS [DayOfWeek]  -- list of days-of-week expanded from @crontabExpr
  FROM [values] WHERE FieldName = 'DAYS-OF-WEEK'
) w CROSS APPLY (
  SELECT DATEADD(SECOND, -(DATEPART(SECOND, @after)), DATEADD(MILLISECOND, -(DATEPART(MILLISECOND, @after)), @after)) AS StartMinute
) cutoff CROSS APPLY ( 
  -- Field value lists are CROSS JOINed above to generate all matching date patterns.
  -- Here we translate the patterns into dates, but use the 1st of the month instead
  -- of the day from the pattern (because we don't know at this point if the month
  -- has a day of that number; e.g. there is no February 30th).
 
  -- generate a date during this year
  SELECT
    DATETIMEFROMPARTS(
      DATEPART(YEAR, StartMinute)
    , mth.[Month]
    , 1
    , h.[Hour]
    , m.[Minute]
    , 0, 0
    ) AS [ScheduledDateTime]
    , d.[DayOfMonth]
 
  UNION ALL
 
  -- generate a date during next year (in case we're nearly there)
  SELECT
    DATETIMEFROMPARTS(
      DATEPART(YEAR, StartMinute) + 1
    , mth.[Month]
    , 1
    , h.[Hour]
    , m.[Minute]
    , 0, 0
    ) AS [ScheduledDateTime]
    , d.[DayOfMonth]
 
) proto CROSS APPLY (
  -- determine whether the each month has a day of the number from the pattern, and 
  -- if so build it (this protects us from invalid DATETIMEFROMPARTS construction)
  SELECT 
    CASE
      WHEN DATEPART(MONTH, proto.[ScheduledDateTime] - 1 + proto.[DayOfMonth]) = DATEPART(MONTH, proto.[ScheduledDateTime])
        THEN proto.[ScheduledDateTime] - 1 + proto.[DayOfMonth]
    END AS [ScheduledDateTime]
) validated
WHERE DATEPART(WEEKDAY, validated.[ScheduledDateTime]) = w.[DayOfWeek] + 1  -- crontab weekdays from 0
  -- note that where day-of-month & weekday are both specified, this returns only dates matching *both* criteria
AND validated.[ScheduledDateTime] >= StartMinute  -- (also eliminates NULLs introduced by [validated])
ORDER BY NextOccurrence

tvf_CrontabFieldValues

Helper function for tvf_GetNextOccurrence. Could do a better job at excluding badly-formatted fields – this implementation accepts anything that simply contains no invalid characters. For example, 1* in the HOURS field is accepted but expands (counterintuitively) to 10-23 (1, followed by * expanded to 0-23). 

/*
 * Translate a single crontab field into a corresponding list of values
 * (c) Richard Swinbank 2019-2020
 * http://richardswinbank.net
 */
CREATE   FUNCTION [dbo].[tvf_CrontabFieldValues] (
  @fieldValue NVARCHAR(255)
, @crontabField VARCHAR(25)
) RETURNS TABLE
AS RETURN
 
WITH ranges AS (
  -- translate value ranges into min/max pairs
  SELECT 
    TRY_CAST(IIF(CHARINDEX('-', rm.[Range]) > 0, LEFT(rm.[Range], CHARINDEX('-', rm.[Range]) - 1), rm.[Range]) AS INT) AS MinValue
  , TRY_CAST(IIF(CHARINDEX('-', rm.[Range]) > 0, RIGHT(rm.[Range], LEN(rm.[Range]) - CHARINDEX('-', rm.[Range])), rm.[Range]) AS INT) AS MaxValue
  , TRY_CAST([Modulo] AS INT) AS [Modulo]
  FROM (
    -- split the "every nth iteration" value off (if any)
    SELECT
      IIF(CHARINDEX('/', c.FieldPart) > 0, LEFT(c.FieldPart, CHARINDEX('/', c.FieldPart) - 1), c.FieldPart) AS [Range]
    , IIF(CHARINDEX('/', c.FieldPart) > 0, RIGHT(c.FieldPart, LEN(c.FieldPart) - CHARINDEX('/', c.FieldPart)), NULL) AS [Modulo]
    FROM (
      -- parse a comma-separated list into separate components
      SELECT
        REPLACE(
          SUBSTRING(
            @fieldValue
          , LAG(N, 1, 0) OVER (ORDER BY N) + 1
          , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
          )
        , '*', '0-59') AS FieldPart
      FROM dbo.TinyIntegers
      WHERE SUBSTRING(@fieldValue + ',', N, 1) = ','
    ) c
  ) rm
)
SELECT 
  DISTINCT n.N
FROM ranges r
  INNER JOIN (
    -- generate a list of all possible integers (0-59 is the largest range) 
    SELECT TOP 60 N - 1 AS N
    FROM dbo.TinyIntegers
  ) n ON n.N BETWEEN r.MinValue AND r.MaxValue
WHERE (r.[Modulo] IS NULL OR n.N % r.Modulo = 0)  -- run every r.[Modulo] iterations
AND n.N BETWEEN IIF(@crontabField IN ('DAYS', 'MONTHS'), 1, 0) AND
  CASE @crontabField
    WHEN 'MINUTES' THEN 59
    WHEN 'HOURS' THEN 23
    WHEN 'DAYS' THEN 31
    WHEN 'MONTHS' THEN 12
    WHEN 'DAYS-OF-WEEK' THEN 6
  END  -- run during intervals
AND @fieldValue NOT LIKE '%[^0-9,\-/*]%' ESCAPE '\'  -- refuse badly-formatted fields (mostly...)
AND @fieldValue NOT LIKE '[\-/,]%' ESCAPE '\'
AND @fieldValue NOT LIKE '%[\-/,]' ESCAPE '\'

What is dbo.TinyIntegers?

The code here makes use of a table called dbo.TinyIntegers. This is a small numbers table:

  • column [N] is of type TINYINT
  • it contains integers from 1 to 255 (no zero!) 
CREATE TABLE dbo.TinyIntegers (
  N TINYINT PRIMARY KEY
)