Convert hh:mm:ss to seconds using a SQL Function

The hardest part with creating reports is when you have to deal with inconsistent data in fields. When expecting time to be represented as hh:mm:ss (e.g. 01:02:03) and instead you receive a mix of h:mm:ss (1:02:03) and mm:ss (62:03), it’s easiest to use a function that converts the inconsistent time data to a value that’s easier to report on, or run subsequent calculations on.

In the example below, the SQL function does a quick check to count the number of occurrences of the time separator, in this case ‘:’, and then proceeds to calculate each time partition as the corresponding seconds value.

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

Any suggestions for a more efficient way to calculate the final seconds conversion?

Navigation