--\
---) Author: Hans Michiels
---) With this script you can correct a SQL Agent jobstep that has a DateTime parameter
---) and would otherwise be uneditable due to a bug in SQL Server.
---) You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
--/
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
UPDATE js
SET command = REPLACE(js.command, expr3.original_date, expr5.corrected_date)
FROM [msdb].[dbo].[sysjobsteps] AS js
CROSS APPLY ( SELECT
CHARINDEX('(DateTime)', js.command) AS [datetime_pos]
) AS expr1
CROSS APPLY ( SELECT
CHARINDEX(';”\”', js.command, expr1.datetime_pos + 1) AS [start_pos],
CHARINDEX(' ', js.command, expr1.datetime_pos + 1) AS [end_pos]
) AS expr2
CROSS APPLY ( SELECT
SUBSTRING(js.command, expr2.start_pos + 4, expr2.end_pos - start_pos - 4) AS [original_date]
) AS expr3
CROSS APPLY ( SELECT
CASE
WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 4, 1) = '-'
THEN '0' + SUBSTRING(expr3.[original_date], 1, 1) + '-0' + SUBSTRING(expr3.[original_date], 3, 6)
WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
THEN '0' + expr3.[original_date]
WHEN SUBSTRING(expr3.[original_date], 3, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
THEN SUBSTRING(expr3.[original_date], 1, 3) + '0' + SUBSTRING(expr3.[original_date], 4, 6)
ELSE expr3.[original_date]
END AS [fixedwidth_date]
) AS expr4
CROSS APPLY ( SELECT
CONVERT(CHAR(10),
CONVERT(DATE, SUBSTRING(expr4.[fixedwidth_date], 7, 4)
+ SUBSTRING(expr4.[fixedwidth_date], 4, 2)
+ SUBSTRING(expr4.[fixedwidth_date], 1, 2), 112)
, 120) AS [corrected_date]
) expr5
WHERE js.subsystem = 'SSIS'
AND expr1.datetime_pos > 0
AND expr2.start_pos > 0
AND expr2.end_pos > expr2.start_pos
AND SUBSTRING(expr4.fixedwidth_date, 3, 1) = '-'
AND SUBSTRING(expr4.fixedwidth_date, 6, 1) = '-'