Statement to convert float time value into a real TIME field

May 31, 2024

Some old time data stored as float needs to be converted into time fields. So, given the table:

CREATE TABLE #T (TVALUE FLOAT(8), EXPECTED_TVALUE TIME);
INSERT INTO #T (TVALUE, EXPECTED_TVALUE) 
VALUES 
(0, null), 
(9.474, '09:47:40'), 
(11.3015, '11:30:15'), 
(1.0, '01:00:00'), 
(13, '13:00:00');

Is it possible to write a statement that for all these possible values the desired outcome is achieved?

I came up with some parts of it but none is valid or leads me to a correct working statement:

SELECT TVALUE, EXPECTED_TVALUE, FORMAT(FLOOR(TVALUE) * 10000 + (TVALUE - FLOOR(TVALUE)) * 60 + (TVALUE - FLOOR(TVALUE)) * 60, '00:00:00') AS TMP_TVALUE FROM #T

SELECT TVALUE, EXPECTED_TVALUE, CAST(CONVERT(VARCHAR,DATEADD(SECOND, TVALUE * 3600, 0),108) AS TIME) AS TMP_TVALUE FROM #T

SELECT TVALUE, EXPECTED_TVALUE, FORMAT(TVALUE, '00:00:00') as TMP_TVALUE FROM #T

I’d rather not use functions or procedures since it must be part of a (much) larger migration script.

EDIT / DISCLAIMER

The given float type is historical data. Created some 20+ years ago. It’s a given fact. My job is to converted it to a time type.

>Solution :

First of all, you’ll have to inspect all the stored values, or at least check for minimums, maximums and values above 24.0. Expecting 0 to map to NULL instead of 00:00:00 suggests there are more quirks than just loss of precision. There may be other magic values like this.

Even the sysjobschedules table that hasn’t changed a lot since the 1990s uses int to store time as digits, eg 1154 for 11:54. Using FLOAT(8) was a matter of choice, not age. There may be a reason for this.

You can use some multiplication and modulo operations to extract the various digit pairs and use TIMEFROMPARTS to create a time value without string formatting.

The following query :

select * ,
    round(tvalue,0,1) as H,
    cast(round(tvalue*100,0,1) as int) % 100 as M,
    cast(round(tvalue*10000,0,1) as int) % 100 as S,
    timefromparts(
        round(tvalue,0,1),
        cast(round(tvalue*100,0,1) as int) % 100,
        cast(round(tvalue*10000,0,1) as int) % 100,0,0) as Time
from #t

Returns :

TVALUE  EXPECTED_TVALUE     H   M   S   Time
0       NULL                0   0   0   00:00:00
9.474   09:47:40.0000000    9   47  40  09:47:40
11.3015 11:30:15.0000000    11  30  15  11:30:15
1       01:00:00.0000000    1   0   0   01:00:00
13      13:00:00.0000000    13  0   0   13:00:00

The only difference is 0 and NULL. 00:00:00 is a perfectly valid time, not NULL.

You can use IIF(TValue=0, NULL, TIMEPART(...)) to convert 0 to null