Expand CronTab expressions with SQL / T-SQL

CronTab-Expander.sql

USE Exercises
GO
/* ***********************************************************************************************
 *
 * CRONTAB EXPANDER
 *
 * ***********************************************************************************************/


/**************************************************************************************************
 * f_CrontabExpander
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		takes a string containing a crontab expression 
 *		and expands it to a sequence of datetime-values
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_CrontabExpander') IS NOT NULL DROP FUNCTION f_CrontabExpander;
GO
CREATE FUNCTION f_CrontabExpander (@crontabExpression nvarchar(255))
RETURNS @tbl TABLE (dt DATETIME)
AS BEGIN

	DECLARE @now Datetime
	SELECT @now = dbo.f_dtCutSeconds(GETDATE())

	-- CronTab Expression
	DECLARE @cron nvarchar(255) = @crontabExpression; --'0/10 0-6,19-23 * * 1-5'

	-- declare vars for crontab-expression segments
	DECLARE @mns nvarchar(50), @hrs nvarchar(50), @dom nvarchar(50), @mon nvarchar(50), @wds nvarchar(50)

	-- expand crontab-expression segments
	DECLARE @tMinutes TABLE (value int)
	SELECT @mns = dbo.f_returnItemByIndex(@cron, ' ', 1)
	INSERT @tMinutes (value) SELECT value FROM f_expandCronSegment(@mns, 0, 59);

	DECLARE @tHours TABLE (value int)
	SELECT @hrs = dbo.f_returnItemByIndex(@cron, ' ', 2)
	INSERT @tHours (value) SELECT value FROM f_expandCronSegment(@hrs, 0, 24);

	DECLARE @tDays TABLE (value int)
	SELECT @dom = dbo.f_returnItemByIndex(@cron, ' ', 3)
	INSERT @tDays (value) SELECT value FROM f_expandCronSegment(@dom, 1, 31);

	DECLARE @tMonths TABLE (value int)
	SELECT @mon = dbo.f_returnItemByIndex(@cron, ' ', 4)
	INSERT @tMonths (value) SELECT value FROM f_expandCronSegment(@mon, 1, 12);

	DECLARE @tWeekdays TABLE (value int)
	SELECT @wds = dbo.f_returnItemByIndex(@cron, ' ', 5)
	INSERT @tWeekdays (value) SELECT value FROM f_expandCronSegment(@wds, 1, 7);

	-- create a datesequence of minutes with parts to match
	;WITH datesequence AS (
		SELECT number, dt = DATEADD(minute, number, @now),
			mns = CONVERT(nvarchar, DATEPART(minute, DATEADD(minute, number, @now))),
			hrs = CONVERT(nvarchar, DATEPART(hour, DATEADD(minute, number, @now))),
			dom = CONVERT(nvarchar, DAY(DATEADD(minute, number, @now))), 
			mon = CONVERT(nvarchar, MONTH(DATEADD(minute, number, @now))),
			wds = CONVERT(nvarchar, DATEPART(weekday, DATEADD(minute, number, @now)))
		FROM f_numbersTable(1, 100000)
	)
	-- and insert only matching dates into resultset
	INSERT @tbl (dt)
	SELECT TOP 1000 dt FROM dateSequence
	WHERE (	'minute match' = (
				CASE WHEN mns IN (SELECT value FROM @tMinutes) 
					 THEN 'minute match' ELSE 'no match' END))
	  AND (	'hour match' = (
				CASE WHEN hrs IN (SELECT value FROM @tHours) 
					 THEN 'hour match' ELSE 'no match' END))
	  AND (	'day match' = (
				CASE WHEN dom IN (SELECT value FROM @tDays) 
					 THEN 'day match' ELSE 'no match' END))
	  AND (	'month match' = (
				CASE WHEN mon IN (SELECT value FROM @tMonths) 
					 THEN 'month match' ELSE 'no match' END))
	  AND (	'weekday match' = (
				CASE WHEN wds IN (SELECT value FROM @tWeekdays) 
					 THEN 'weekday match' ELSE 'no match' END))
	ORDER BY dt ASC

	RETURN
END
GO


**************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *      Helper-function: takes the first matching datetime 
 *		value from an expanded crontab expression 
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_GetFirstMatchingCrontabDatetime') IS NOT NULL
	DROP FUNCTION f_GetFirstMatchingCrontabDatetime;
GO
CREATE FUNCTION f_GetFirstMatchingCrontabDatetime (@crontabExpression nvarchar(255))
RETURNS datetime
AS BEGIN
	DECLARE @result datetime
	SELECT TOP 1 @result = dt FROM f_crontabExpander(@crontabExpression)
	RETURN @result
END
GO

Expanders.sql

/* ***********************************************************************************************
 *
 * EXPANDERS
 *
 * ***********************************************************************************************
 *
 *		Expanders take segments of a single crontab expression, create a 
 *		vector of values and return a table. Duplicate values are removed.
 *
 *		Examples: 
 *
 *			* Minutes:		'0/15'		-->	0,15,30,45
 *			* Hours:		'8-18'		-->	8,9,10,11,12,13,14,15,16,17,18
 *			* Days:			'1-10'		-->	1,2,3,4,5,6,7,8,9,10
 *			* Months:		'1/3'		-->	1,4,7,10
 *							'JAN,JUL'	-->	1,7
 *			* Weekdays:		'*'			--> 0,1,2,3,4,5,6
 *							'1-3'		-->	1,2,3
 *							'SUN-TUE'	-->	0,1,2
 *
 * ***********************************************************************************************/
USE Exercises
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO



/**************************************************************************************************
 * f_expandCronSegment
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		expands a single part of standard crontab-expressions: either minutes, hours,  
 *		days, months or weekdays - containing star (*), sequence (1,2,3), range (1/1) 
 *		and step (1-2) - tokens. Spplits up sequence and handles every part of the 
 *		expression by running it through all of the token expanders.
 *
 *		String equivalents of months (JAN, FEB ...) and weekdays (SUN, MON ...) 
 *		are handled properly. Duplicates are removed. 
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_expandCronSegment') IS NOT NULL 
	DROP FUNCTION f_expandCronSegment;
GO
CREATE FUNCTION f_expandCronSegment (@expr nvarchar(255), @min int, @max int) 
RETURNS @tbl TABLE (value int)
AS BEGIN

	SET @expr = dbo.f_replaceTerms(@expr);

	INSERT @tbl (value) 
	SELECT DISTINCT value = CONVERT(int, value) FROM (

		SELECT value FROM f_expandSimpleExpression(@expr, @min, @max)
		UNION
		SELECT DISTINCT value FROM f_expandStarExpression(@expr, @min, @max)
		UNION
		SELECT DISTINCT value FROM f_splitStringToTable(@expr, ',')
		WHERE ISNUMERIC(value) = 1 AND value BETWEEN @min AND @max
		UNION
		SELECT DISTINCT expanded.value FROM f_splitStringToTable(@expr, ',') expressions
		CROSS APPLY f_expandStepExpression(expressions.value, @min, @max) expanded
		UNION
		SELECT DISTINCT expanded.value FROM f_splitStringToTable(@expr, ',') expressions
		CROSS APPLY f_expandRangeExpression(expressions.value, @min, @max) expanded
	) AS u
	ORDER BY value
	
	RETURN
END
GO


/**************************************************************************************************
 * f_replaceTerms
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		Maps string equivalents of months (JAN, FEB ...) 
 *		and weekdays (SUN, MON ...) to numbers.
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_replaceTerms') IS NOT NULL 
	DROP FUNCTION f_replaceTerms;
GO
CREATE FUNCTION f_replaceTerms (@expr nvarchar(100)) 
RETURNS nvarchar(100)
AS BEGIN
	RETURN 
		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		REPLACE(UPPER(@expr), 'JAN', '1'),
		'FEB', '2'),'MAR', '3'),'APR', '4'),'MAY', '5'),'JUN', '6'),'JUL', '7'),
		'AUG', '8'),'SEP', '9'),'OCT', '10'),'NOV', '11'),'DEC', '12'),'SUN', '0'),
		'MON', '1'),'TUE', '2'),'WED', '3'),'THU', '4'),'FRI', '5'),'SAT', '6')
END
GO



/**************************************************************************************************
 * f_expandSimpleExpression
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		Token expander: Takes an expression containing a simple number (days: 1) 
 *		and returns a table with this number. Returns an empty table if the 
 *		expression is no simple number or out of bounds.
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_expandSimpleExpression') IS NOT NULL 
	DROP FUNCTION f_expandSimpleExpression;
GO
CREATE FUNCTION f_expandSimpleExpression (@expr nvarchar(100), @min int, @max int) 
RETURNS @tbl TABLE (value int)
AS BEGIN

	IF @expr = '*' OR @min IS NULL OR @max IS NULL or @min > @max RETURN;
	IF CHARINDEX(',', @expr, 1) + CHARINDEX('/', @expr, 1) + CHARINDEX('-', @expr, 1) > 0 RETURN;
	IF CONVERT(int, @expr) < @min OR CONVERT(int, @expr) > @max RETURN;

	INSERT @tbl (value) SELECT value = CONVERT(int, @expr)

	RETURN	
END
GO


/**************************************************************************************************
 * f_expandStarExpression
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		Token expander: Takes a token containing a star (weekdays: '*') 
 *		and returns a table with all numbers between @min and @max.
 *		Returns an empty table if the expression is no star.
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_expandStarExpression') IS NOT NULL 
	DROP FUNCTION f_expandStarExpression;
GO
CREATE FUNCTION f_expandStarExpression (@expr nvarchar(100), @min int, @max int) 
RETURNS @tbl TABLE (value int)
AS BEGIN

	IF @expr <> '*' OR @min IS NULL OR @max IS NULL or @min > @max RETURN;

	WITH Starter(value) AS (
		SELECT @min AS value
		UNION ALL
		SELECT value + 1 FROM Starter
		WHERE value + 1 <= @max
	)
	INSERT @tbl (value) SELECT value FROM Starter

	RETURN	
END
GO


/**************************************************************************************************
 * f_expandStepExpression
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		Token expander: Takes a step token (months: '1/3') and returns a table 
 *		with all single numbers between @min and @max. Returns an empty table 
 *		if the expression does not contain a step token.
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_expandStepExpression') IS NOT NULL 
	DROP FUNCTION f_expandStepExpression;
GO
CREATE FUNCTION f_expandStepExpression (@expr nvarchar(100), @min int, @max int) 
RETURNS @tbl TABLE (value int)
AS BEGIN

	IF CHARINDEX('/', @expr, 1) = 0 RETURN;

	DECLARE @start int, @step int;
	SELECT @start = dbo.f_ReturnItemByIndex(@expr, '/', 1), @step = dbo.f_ReturnItemByIndex(@expr, '/', 2)
	IF @start IS NULL OR @step IS NULL or @start < @min RETURN;

	WITH Stepper(value) AS (
		SELECT @start AS value
		UNION ALL
		SELECT value + @step FROM Stepper
		WHERE value + @step <= @max
	)
	INSERT @tbl (value) SELECT value FROM Stepper

	RETURN	
END
GO


/**************************************************************************************************
 * f_expandRangeExpression
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		Token expander: Takes a range token (months: '1/3') and returns a table with 
 *		all single numbers between @min and @max. Returns an empty table if the 
 *		expression does not contain a step token.
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_expandRangeExpression') IS NOT NULL 
	DROP FUNCTION f_expandRangeExpression;
GO
CREATE FUNCTION f_expandRangeExpression (@expr nvarchar(100), @min int, @max int) 
RETURNS @tbl TABLE (value int)
AS BEGIN

	IF CHARINDEX('-', @expr, 1) = 0 RETURN;

	DECLARE @from int, @to int;
	SELECT @from = dbo.f_ReturnItemByIndex(@expr, '-', 1), @to = dbo.f_ReturnItemByIndex(@expr, '-', 2)
	IF @from IS NULL OR @to IS NULL or @from < @min RETURN;

	WITH Ranger(value) AS (
		SELECT @from AS value
		UNION ALL
		SELECT value + 1 FROM Ranger
		WHERE value + 1 <= @to
	)
	INSERT @tbl (value) SELECT value FROM Ranger

	RETURN	
END
GO

-- Test
-- SELECT value FROM f_expandSimpleExpression('59', 0, 59)
-- SELECT value FROM f_expandSimpleExpression('61', 0, 59)
-- SELECT value FROM f_expandCronSegment('0,1,2,3-8,14/7,61', 0, 59)

Sequencers.sql

/* ***********************************************************************************************
 *
 * SEQUENCERS
 *
 *************************************************************************************************/
USE Exercises
GO

/**************************************************************************************************
 * f_DateSequenceGenerator
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		generates a sequence of datetime values between
 *      fromDate and toDate using a numbers table
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_DateSequenceGenerator') IS NOT NULL
	DROP FUNCTION f_DateSequenceGenerator;
GO
CREATE FUNCTION f_DateSequenceGenerator (	
	@datepart varchar(12), @fromDate DATETIME, 
    @toDate DATETIME, @offsetToNow int = 1
)
RETURNS @reTable TABLE (datum DATETIME)
AS BEGIN

	IF UPPER(@datepart) = 'MONTH' OR @datepart = 'mm' OR @datepart = 'm'
	BEGIN
		INSERT @reTable (datum)
		SELECT datum FROM (
			SELECT datum = DATEFROMPARTS(yearNumber, monthNumber, 1) 
			FROM (
				SELECT DISTINCT yearNumber = y.number, monthNumber FROM f_NumbersTable(YEAR(@fromDate), YEAR(@toDate)) AS y
				CROSS JOIN (SELECT DISTINCT monthNumber = number FROM f_NumbersTable(1,12)) AS m
			) AS u1
		) AS u
		WHERE datum BETWEEN @FromDate AND @toDate
	END

	IF UPPER(@datepart) = 'DAY' OR UPPER(@datepart) = 'DAY' OR UPPER(@datepart) = 'DD'
	BEGIN
		INSERT @reTable (datum)
		SELECT datum = DATEADD(DAY, number - 1 + @offsetToNow, @FromDate) 
		FROM f_NumbersTable(1, DATEDIFF(DAY, @fromDate, DATEADD(DAY, 1, @toDate)))
	END

	IF UPPER(@datepart) = 'MINUTE' OR UPPER(@datepart) = 'MIN'
	BEGIN
		INSERT @reTable (datum)
		SELECT datum = DATEADD(MINUTE, number - 1 + @offsetToNow, @FromDate) 
		FROM f_NumbersTable(1, DATEDIFF(MINUTE, @fromDate, DATEADD(DAY, 1, @toDate)))
		WHERE DATEADD(MINUTE, number - 1 + @offsetToNow, @FromDate) < @toDate
	END

	IF UPPER(@datepart) = 'SECOND' OR UPPER(@datepart) = 'SEC'
	BEGIN
		INSERT @reTable (datum)
		SELECT datum = DATEADD(MINUTE, number - 1 + @offsetToNow, @FromDate) 
		FROM f_NumbersTable(1, DATEDIFF(SECOND, @fromDate, DATEADD(SECOND, 1, @toDate)))
	END


	RETURN
END

GO


/* ***********************************************************************************************
 *
 * NUMBERS
 *
 * ***********************************************************************************************/



/**************************************************************************************************
 * p_createNumbersTable
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		create a Numbers table
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.p_createNumbersTable') IS NOT NULL
	DROP PROCEDURE p_createNumbersTable;
GO
CREATE PROCEDURE p_createNumbersTable
( @upperBound INT = 3162240 )     -- @upperBound ~ Seconds in 3 years
AS BEGIN
	------ Validate @lowerBound
	----DECLARE @errMsg NVARCHAR(150) = 'Procedure was called with a @lowerBound-Parameter < 0. '
	----				+ 'Only values > 0 are allowed. Numbers table was not (re)generated!';
	----IF @lowerBound < 0 THROW 66601, @errMsg, 16;

	-- Create Numbers table and Index 
	IF OBJECT_ID(N'Exercises.dbo.Numbers', 'U') IS NULL
	BEGIN
		CREATE TABLE Numbers (number INT);
		CREATE UNIQUE CLUSTERED INDEX IX_Numbers ON Numbers (number) 
			WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)
	END

	-- table content is recreated on each call. 
	-- It's not recurring and won't take long anyway
	TRUNCATE TABLE Numbers;

	-- create records
	INSERT Numbers (number)
	SELECT CAST(number AS INT)				
		-- ROW_NUMBER() is BigInt so a CAST is required
	FROM (
		SELECT number = number - 1			
			-- ROW_NUMBER starts with 1, subtract 1 to get 0 as lowerBound
		FROM (
			SELECT number = ROW_NUMBER() OVER (ORDER BY number) 
			FROM (
				-- Sql Server INT max val is 2,147,483,647, so two cross joins 
				-- on spt_values (which contains at least 2050 records) suffice
				SELECT number = ROW_NUMBER() OVER (ORDER BY v1.number) 
				FROM master..spt_values AS v1
				CROSS JOIN (SELECT number FROM master..spt_values) AS v2
				CROSS JOIN (SELECT number FROM master..spt_values) AS v3
			) AS vu
		) AS u1
		WHERE u1.number BETWEEN 1 AND @upperBound + 1
	) as u
	
END
GO

-- Test
-- EXEC p_createNumbersTable
-- SELECT COUNT(*) FROM Numbers




/**************************************************************************************************
 * f_NumbersTable
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		get a sequence of numbers between lowerBound and upperBound
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_numbersTable') IS NOT NULL 
	DROP FUNCTION f_numbersTable;
GO
CREATE FUNCTION f_numbersTable (@lowerBound INT, @upperBound INT)
RETURNS @reTable TABLE (number INT)
AS BEGIN

	INSERT @reTable (number)
	SELECT number FROM Numbers
	WHERE number BETWEEN @lowerBound AND @upperBound

	RETURN

END
GO

-- Test
-- SELECT * FROM f_numbersTable(1,10000)

Splitters.sql

/************************************************************************************************
 *
 * SPLITTERS
 *
 ************************************************************************************************



/**************************************************************************************************
 * f_ReturnItemByIndex
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		splits a string by delimiter and returns the element at the index
 *
 **************************************************************************************************/
IF OBJECT_ID (N'Exercises.dbo.f_ReturnItemByIndex') IS NOT NULL
   DROP FUNCTION f_ReturnItemByIndex
GO
CREATE FUNCTION f_ReturnItemByIndex(@Input nvarchar(4000), @Delimiter nvarchar(1) = ',', @Index int) 
RETURNS nvarchar(4000) 
BEGIN

	DECLARE @Item nvarchar(4000), @curIndex int = 0, @curPos int = 1, @nextPos int = 0;

	SET @nextPos = CHARINDEX(@Delimiter, @Input, @curPos);

	IF @nextPos = 0 BEGIN
		IF @Index = 1 RETURN @Input;
		RETURN NULL;
	END

	WHILE @nextPos > 0 BEGIN
		
		SET @curIndex = @curIndex + 1;
		
		If @curIndex = @Index 
            RETURN RTRIM(LTRIM(SUBSTRING(@Input, @curPos, @nextPos - @curPos)));

		SET @curPos = @nextPos + 1;
		SET @nextPos = CHARINDEX(@Delimiter, @Input, @curPos + 1);

		IF @nextPos = 0 AND @curIndex = @Index - 1 SET @nextPos = LEN(@Input) + 1;
	END

	RETURN NULL

END
GO


/**************************************************************************************************
 * f_SplitStringToTable
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		splits a string by delimiter and returns the elements 
 *		in a table with position and value
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_SplitStringToTable') IS NOT NULL 
	DROP FUNCTION f_SplitStringToTable;
GO
CREATE FUNCTION f_splitStringToTable (@Input nvarchar(4000), @Delimiter nchar(1) = ',') 
RETURNS TABLE AS 
RETURN (

	WITH Split(begpos, endpos) AS (
		SELECT 0 AS begpos, CHARINDEX(@Delimiter, @Input) AS endpos
		UNION ALL
		SELECT endpos + 1, CHARINDEX(@Delimiter, @Input, endpos+1)
		FROM Split
		WHERE endpos > 0
	)
	SELECT position = begPos, 
		[value] = SUBSTRING(@Input, begpos, COALESCE(NULLIF(endpos, 0), LEN(@Input) + 1) = begpos)
	FROM Split
)
GO


/**************************************************************************************************
 * f_dtCutSeconds
 **************************************************************************************************
 * Author/Date:		Manfred Kipfelsberger / 2017-04-03
 * Web:				www.manfredkipfelsberger.de
 *-------------------------------------------------------------------------------------------------
 * Description:
 *
 *		takes a datetime value and returns a datetime with seconds cut off
 *
 **************************************************************************************************/
IF OBJECT_ID('Exercises.dbo.f_dtCutSeconds') IS NOT NULL
	DROP FUNCTION f_dtCutSeconds;
GO
CREATE FUNCTION f_dtCutSeconds (@dt datetime)
RETURNS datetime
AS BEGIN
	DECLARE @result datetime = CONVERT(datetime, CONVERT(date, @dt))
	RETURN DATEADD(minute, DATEPART(minute, @dt), DATEADD(hour, DATEPART(hour, @dt), @result))
END
GO