Convert varchar time (for time > 24 hours) to float

Question

I have a column with time records that represent the difference between two dates. Some of these records exceed 24 hours.

As time syntax doesn't account for time records > 24 hours, I've had to convert these records to varchar hh:mm:ss as outlined in the solution in the link below:

SQL date format [h]:mm:ss like Excel does, beyond 24 hr

This works fine, however my problem is I now need to convert these to float (e.g. 69:00:00.0000 as 2.875).

Unfortunately I can't use the commonly suggested datediff to do this as it throws an 'out of range' error for the records exceeding 24 hours.

Any ideas on how I can overcome this?

Answer 1

Thanks for the helpful posts, I took the advice of the original comments in that it's a lot easier to calculate the difference between dates as a number and convert this later in the presentation layer (Excel).

To do this, I used DATEPART logic to sum day,hour,minute,second together to derive a 'decimal day' figure then used Excel to convert this to [h]:mm:ss

(e.g. difference between '2016-04-30 23:23:00' and '2016-04-30 23:25:00' is 2 minutes. This is 0.0013888833 as a decimal day, which converts to 0:02:00 in Excel when formatted as [h]:mm:ss)

Unfortunately I don't have time to test whether the alternative solutions provided work.

Answer 2

Something like this?

DECLARE @YourTime VARCHAR(100)='69:00:00.0000';

WITH Splitted AS
(
    SELECT @YourTime AS t
          ,CHARINDEX(':',@YourTime)-1 AS HourLength
          ,LEFT(@YourTime,CHARINDEX(':',@YourTime)-1) AS HourPart
)
SELECT HourPart/24 + CAST(CAST(CAST(STUFF(@YourTime,1,HourLength,HourPart-(HourPart/24)*24) AS TIME) AS DATETIME) AS FLOAT)
FROM Splitted

UPDATE the same as function

CREATE FUNCTION dbo.ConvertExceedingTimeToFloat(@TimeString VARCHAR(100))
RETURNS FLOAT
AS
BEGIN
    DECLARE @RetVal FLOAT;
    WITH Splitted AS
    (
        SELECT @TimeString AS t
              ,CHARINDEX(':',@TimeString)-1 AS HourLength
              ,LEFT(@TimeString,CHARINDEX(':',@TimeString)-1) AS HourPart
    )
    SELECT @RetVal = HourPart/24 + CAST(CAST(CAST(STUFF(@TimeString,1,HourLength,HourPart-(HourPart/24)*24) AS TIME) AS DATETIME) AS FLOAT)
    FROM Splitted;

    RETURN @RetVal;
END
GO

SELECT dbo.ConvertExceedingTimeToFloat('69:00:00.0000')
GO

DROP FUNCTION dbo.ConvertExceedingTimeToFloat;

Answer 3

Maybe you could REPLACE : to .. This will give you a string that could be parsed with PARSENAME (starting with SQL Server 2012)), then CAST to float:

DECLARE @SomeTime VARCHAR(100)='69:00:00.0000'

SELECT CAST(PARSENAME(REPLACE(@SomeTime,':','.'),4)/24.00 as float)

Or SUBSTRING with CHARINDEX:

SELECT CAST(SUBSTRING(@SomeTime,1,CHARINDEX(':',@SomeTime)-1)/24.00 as float)

Output:

2,875

Hope, I get your question right.

EDIT

The above method used only for hours. If you need to go deeper:

DECLARE @SomeTime VARCHAR(100)='69:55:10.999',
        @x xml
--Convert to XML
SELECT @x = CAST('<p>'+REPLACE(REPLACE(@SomeTime,':','.'),'.','</p><p>') +'</p>' as xml)
--Working with XML, each part need convertion
SELECT  CAST(
        t.c.value('/p[1]','int')/24.00+
        (t.c.value('/p[2]','int')/60.00)/24.00+
        ((t.c.value('/p[3]','int')/60.00)/60.00)/24.00+
        (((t.c.value('/p[4]','int')/1000.00)/60.00)/60.00)/24.00
        as float) result
FROM @x.nodes('/') as t(c)

Output:

2,9133217194375

NOTE:

I am know not much about MATH in SQL Server, please, report/suggest if you see any flows in current solution.