Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
tsql:crontab [2019/10/22 23:03]
admin [tvf_CrontabFieldValues]
tsql:crontab [2020/01/23 09:49] (current)
admin
Line 14: Line 14:
 Passing these parameters into ''​tvf_GetNextOccurrence''​ returns a list of the first ''​@limit''​ dates & times indicated by ''​@crontabExpr''​ after ''​@after''​. ​ Passing these parameters into ''​tvf_GetNextOccurrence''​ returns a list of the first ''​@limit''​ dates & times indicated by ''​@crontabExpr''​ after ''​@after''​. ​
  
-<code tsql> +<code tsql>/*
-/*+
  * Return the next few start date/times indicated by a crontab expression  * Return the next few start date/times indicated by a crontab expression
- * (c) Richard Swinbank 2019+ * (c) Richard Swinbank 2019-2020
  * http://​richardswinbank.net  * http://​richardswinbank.net
  */  */
-CREATE ​OR ALTER FUNCTION tvf_GetNextOccurrence (+CREATE FUNCTION ​[dbo].[tvf_GetNextOccurrence(
   @after DATETIME   @after DATETIME
 , @crontabExpr VARCHAR(255) , @crontabExpr VARCHAR(255)
Line 26: Line 25:
 ) RETURNS TABLE ) RETURNS TABLE
 AS RETURN AS RETURN
 + 
 WITH fields AS ( WITH fields AS (
   -- split incoming expression into its five fields   -- split incoming expression into its five fields
Line 32: Line 31:
     SUBSTRING(     SUBSTRING(
       @crontabExpr       @crontabExpr
-    , LAG(n, 1, 0) OVER (ORDER BY N) + 1 +    , LAG(N, 1, 0) OVER (ORDER BY N) + 1 
-    , N - LAG(n, 1, 0) OVER (ORDER BY N) - 1+    , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
     ) AS FieldValue     ) AS FieldValue
   , CASE ROW_NUMBER() OVER (ORDER BY N)   , CASE ROW_NUMBER() OVER (ORDER BY N)
Line 42: Line 41:
       WHEN 5 THEN '​DAYS-OF-WEEK'​       WHEN 5 THEN '​DAYS-OF-WEEK'​
     END AS FieldName     END AS FieldName
-  FROM +  FROM dbo.TinyIntegers ​n
-    SELECT +
-      ROW_NUMBER() OVER (ORDER BY [object_id]) AS N +
-    FROM sys.all_columns +
-  ) n+
   WHERE SUBSTRING(REPLACE(@crontabExpr,​ ' ', '​x'​) + '​x',​ N, 1) = '​x'  ​   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     -- really we're looking for spaces, but turn them into '​x'​ because T-SQL doesn'​t always play nicely with them
Line 75: Line 70:
   SELECT [Value] AS [DayOfWeek] ​ -- list of days-of-week expanded from @crontabExpr   SELECT [Value] AS [DayOfWeek] ​ -- list of days-of-week expanded from @crontabExpr
   FROM [values] WHERE FieldName = '​DAYS-OF-WEEK'​   FROM [values] WHERE FieldName = '​DAYS-OF-WEEK'​
-) w CROSS APPLY ( +) 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.   -- 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   -- 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   -- 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).   -- has a day of that number; e.g. there is no February 30th).
 + 
   -- generate a date during this year   -- generate a date during this year
   SELECT   SELECT
     DATETIMEFROMPARTS(     DATETIMEFROMPARTS(
-      DATEPART(YEAR, ​@after)+      DATEPART(YEAR, ​StartMinute)
     , mth.[Month]     , mth.[Month]
     , 1     , 1
Line 92: Line 89:
     ) AS [ScheduledDateTime]     ) AS [ScheduledDateTime]
     , d.[DayOfMonth]     , d.[DayOfMonth]
 + 
   UNION ALL   UNION ALL
 + 
   -- generate a date during next year (in case we're nearly there)   -- generate a date during next year (in case we're nearly there)
   SELECT   SELECT
     DATETIMEFROMPARTS(     DATETIMEFROMPARTS(
-      DATEPART(YEAR, ​@after) + 1+      DATEPART(YEAR, ​StartMinute) + 1
     , mth.[Month]     , mth.[Month]
     , 1     , 1
Line 106: Line 103:
     ) AS [ScheduledDateTime]     ) AS [ScheduledDateTime]
     , d.[DayOfMonth]     , d.[DayOfMonth]
 + 
 ) proto CROSS APPLY ( ) proto CROSS APPLY (
   -- determine whether the each month has a day of the number from the pattern, and    -- determine whether the each month has a day of the number from the pattern, and 
Line 117: Line 114:
 ) validated ) validated
 WHERE DATEPART(WEEKDAY,​ validated.[ScheduledDateTime]) = w.[DayOfWeek] + 1  -- crontab weekdays from 0 WHERE DATEPART(WEEKDAY,​ validated.[ScheduledDateTime]) = w.[DayOfWeek] + 1  -- crontab weekdays from 0
-AND validated.[ScheduledDateTime] > @after ​ -- (also eliminates NULLs introduced by [validated])+  -- 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 ORDER BY NextOccurrence
 </​code>​ </​code>​
Line 126: Line 124:
 <code tsql>/* <code tsql>/*
  * Translate a single crontab field into a corresponding list of values  * Translate a single crontab field into a corresponding list of values
- * (c) Richard Swinbank 2019+ * (c) Richard Swinbank 2019-2020
  * http://​richardswinbank.net  * http://​richardswinbank.net
  */  */
-CREATE ​OR ALTER FUNCTION tvf_CrontabFieldValues (+CREATE ​  ​FUNCTION ​[dbo].[tvf_CrontabFieldValues(
   @fieldValue NVARCHAR(255)   @fieldValue NVARCHAR(255)
 , @crontabField VARCHAR(25) , @crontabField VARCHAR(25)
 ) RETURNS TABLE ) RETURNS TABLE
 AS RETURN AS RETURN
 + 
 WITH ranges AS ( WITH ranges AS (
   -- translate value ranges into min/max pairs   -- translate value ranges into min/max pairs
Line 152: Line 150:
           SUBSTRING(           SUBSTRING(
             @fieldValue             @fieldValue
-          , LAG(n, 1, 0) OVER (ORDER BY N) + 1 +          , LAG(N, 1, 0) OVER (ORDER BY N) + 1 
-          , N - LAG(n, 1, 0) OVER (ORDER BY N) - 1+          , N - LAG(N, 1, 0) OVER (ORDER BY N) - 1
           )           )
         , '​*',​ '​0-59'​) AS FieldPart         , '​*',​ '​0-59'​) AS FieldPart
-      FROM +      FROM dbo.TinyIntegers
-        SELECT +
-          ROW_NUMBER() OVER (ORDER BY [object_id]) AS N +
-        FROM sys.all_columns +
-      ) n+
       WHERE SUBSTRING(@fieldValue + ',',​ N, 1) = ','​       WHERE SUBSTRING(@fieldValue + ',',​ N, 1) = ','​
     ) c     ) c
Line 170: Line 164:
   INNER JOIN (   INNER JOIN (
     -- generate a list of all possible integers (0-59 is the largest range) ​     -- generate a list of all possible integers (0-59 is the largest range) ​
-    SELECT TOP 60 +    SELECT TOP 60 - 1 AS N 
-      ROW_NUMBER() OVER (ORDER BY [object_id]) ​- 1 AS N +    FROM dbo.TinyIntegers
-    FROM sys.all_columns+
   ) n ON n.N BETWEEN r.MinValue AND r.MaxValue   ) 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 WHERE (r.[Modulo] IS NULL OR n.N % r.Modulo = 0)  -- run every r.[Modulo] iterations
Line 184: Line 177:
   END  -- run during intervals   END  -- run during intervals
 AND @fieldValue NOT LIKE '​%[^0-9,​\-/​*]%'​ ESCAPE '​\' ​ -- refuse badly-formatted fields (mostly...) AND @fieldValue NOT LIKE '​%[^0-9,​\-/​*]%'​ ESCAPE '​\' ​ -- refuse badly-formatted fields (mostly...)
 +AND @fieldValue NOT LIKE '​[\-/,​]%'​ ESCAPE '​\'​
 +AND @fieldValue NOT LIKE '​%[\-/,​]'​ ESCAPE '​\'​
 </​code>​ </​code>​
  
-{{tag> }}+{{tag> ​tsql}}
 ~~DISCUSSION~~ ~~DISCUSSION~~