Datetime 10min based rounder for SQL Server

tsql_datetime_10min_rounder_udf.sql

IF OBJECT_ID (N'[ts_round_base]', N'FN') IS NOT NULL  
DROP FUNCTION ts_round_base;
GO  
CREATE FUNCTION [dbo].[ts_round_base](@ts datetime, @secs_delta int)  
RETURNS DATETIME
AS
-- =============================================
-- Author:        Arthur Harduim
-- Description:   DATETIME rounding function
--
--
-- Notes:
--
-- DECLARE @sec_delta int = 599; -- Ceil   10min
-- DECLARE @sec_delta int = 240; -- Middle 10min
-- DECLARE @sec_delta int = 0;   -- Floor  10min

-- SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:40:00')) / 10 * 10, 0) '40',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:41:00')) / 10 * 10, 0) '41',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:42:00')) / 10 * 10, 0) '42',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:43:00')) / 10 * 10, 0) '43',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:44:00')) / 10 * 10, 0) '44',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:45:00')) / 10 * 10, 0) '45',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:46:00')) / 10 * 10, 0) '46',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:47:00')) / 10 * 10, 0) '47',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:48:00')) / 10 * 10, 0) '48',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:49:00')) / 10 * 10, 0) '49',
--        DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @sec_delta, '2021-10-01 19:50:00')) / 10 * 10, 0) '50'
--
-- select dbo.ts_10min_ceil('2021-10-01 12:41')  --> '2021-10-01 12:50'
-- select dbo.ts_10min_floor('2021-10-01 12:49') --> '2021-10-01 12:40'
-- select dbo.ts_10min_round('2021-10-01 12:44') --> '2021-10-01 12:40'
-- select dbo.ts_10min_round('2021-10-01 12:46') --> '2021-10-01 12:50'
-- =============================================

BEGIN  
    DECLARE @rounded datetime; 
    SELECT @rounded = DATEADD(MINUTE, DATEDIFF(MINUTE, '1900-01-01', DATEADD(SECOND, @secs_delta, @ts)) / 10 * 10, 0)
    RETURN @rounded;
END; 
GO


IF OBJECT_ID (N'[ts_10min_ceil]', N'FN') IS NOT NULL  
    DROP FUNCTION ts_10min_ceil;
GO  
CREATE FUNCTION [dbo].[ts_10min_ceil](@ts datetime)  
RETURNS datetime
AS
BEGIN 
    DECLARE @rounded datetime;
    SELECT @rounded = dbo.ts_round_base(@ts, 599); 
    RETURN @rounded;
END;
GO

IF OBJECT_ID (N'[ts_10min_floor]', N'FN') IS NOT NULL  
    DROP FUNCTION ts_10min_floor;
GO  
CREATE FUNCTION [dbo].[ts_10min_floor](@ts datetime)  
RETURNS datetime
AS
BEGIN 
    DECLARE @rounded datetime;
    SELECT @rounded = dbo.ts_round_base(@ts, 0); 
    RETURN @rounded;
END; 
GO

IF OBJECT_ID (N'[ts_10min_round]', N'FN') IS NOT NULL  
    DROP FUNCTION ts_10min_round;
GO  
CREATE FUNCTION [dbo].[ts_10min_round](@ts datetime)  
RETURNS datetime
AS
BEGIN 
    DECLARE @rounded datetime;
    SELECT @rounded = dbo.ts_round_base(@ts, 240); 
    RETURN @rounded;
END;
GO