Convert hh:mm:ss to seconds

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    Function to convert inconsistent hh:mm:ss value to seconds
-- Syntax: SELECT dbo.FunctionConvertToSeconds('hh:mm:ss')
-- =============================================
ALTER FUNCTION [dbo].[FunctionConvertToSeconds]
(
    @InputTime varchar(8)
)
RETURNS int
AS
BEGIN

DECLARE @SearchString varchar(1), @SearchStringPosition int, @SearchStringOccurrence int, @InputTimeHour int, @InputTimeMinute int, @InputTimeSecond int, @InputTimeLength int, @OutputSeconds int;

SET @SearchString = ':';

SET @InputTimeLength = LEN(@InputTime);

SET @SearchStringOccurrence = (LEN(@InputTime) - LEN(REPLACE(@InputTime, @SearchString, ''))) /LEN(@SearchString)

IF (@SearchStringOccurrence = 1)
    BEGIN
        IF (@InputTimeLength = 5)
            BEGIN
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeMinute = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTimeSecond = CAST(SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength) AS int);
                SET @OutputSeconds = (@InputTimeMinute * 60) +  @InputTimeSecond;
            END
        IF (@InputTimeLength < 5)
            BEGIN
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeMinute = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTimeSecond = CAST(SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength) AS int);
                SET @OutputSeconds = (@InputTimeMinute * 60) +  @InputTimeSecond;
            END
    END

IF (@SearchStringOccurrence = 2)
    BEGIN
        IF (@InputTimeLength = 8)
            BEGIN
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeHour = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTime = SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength);
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeMinute = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTimeSecond = CAST(SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength) AS int);
                SET @OutputSeconds = (@InputTimeHour * 360) + (@InputTimeMinute * 60) +  @InputTimeSecond;
            END
        IF (@InputTimeLength > 5 AND @InputTimeLength < 8)
            BEGIN
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeHour = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTime = SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength);
                SET @SearchStringPosition = CHARINDEX(@SearchString, @InputTime);
                SET @InputTimeMinute = CAST(SUBSTRING(@InputTime, 0, @SearchStringPosition) AS int);
                SET @InputTimeSecond = CAST(SUBSTRING(@InputTime, @SearchStringPosition + 1, @InputTimeLength) AS int);
                SET @OutputSeconds = (@InputTimeHour * 360) + (@InputTimeMinute * 60) +  @InputTimeSecond;
            END
    END

IF (@SearchStringOccurrence > 2)
    BEGIN
        SET @OutputSeconds = -1;
    END    
            
RETURN @OutputSeconds;
END