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?