Search code examples
sqlsql-servert-sqldatetime-conversion

Trouble converting from time to decimal time

Part of the task I have been given involves performing calculations on a few columns, 2 of which are in the format of hh.mi.ss and they're varchar. In order for the calculations to work, I need to get them into a time decimal format, whereby 1:30 would be 1.5 . Since I'm currently using SQL Server 2005, I don't have the time or data types built-in and I'm unable to get an upgraded version (not my choice). Working with what I have, I've searched around online and tried to convert it but the result isn't accurate. For example, 13.28 becomes (roughly) 13.5, which is great, however, the seconds go to 100 instead of ending at 60 (since I'm converting it to a float).

For example, using 12.57.46, 

CAST(DATEPART(HH, CAST(REPLACE([OASTIM], '.', ':') AS DATETIME)) AS FLOAT) + 
(CAST(DATEPART(MI, CAST(REPLACE([OASTIM], '.', ':') AS DATETIME)) AS FLOAT)/60) + 
(CAST(DATEPART(SS, CAST(REPLACE([OASTIM], '.', ':') AS DATETIME)) AS FLOAT)/3600)

gave me 12.962...

whereas 

CAST(SUBSTRING([OASTIM], 1, 2) AS FLOAT) +
((CAST(SUBSTRING([OASTIM], 4, 5) AS FLOAT) + 
CAST(SUBSTRING([OASTIM], 7, 8) AS FLOAT)/60)/60)

gave me 12.970....

and when I tried something simpler, 

DATEPART(HOUR, CAST(REPLACE([OASTIM], '.', ':') AS DATETIME))+
(DATEPART(MINUTE, CAST(REPLACE([OASTIM], '.', ':') AS DATETIME))/60)

flopped out and gave me only 12

It's my first exposure to Windows SQL and T-SQL, I've been struggling with this for a few hours. As horrible as it sounds, I'm at the point where I'd be happy with it working even it it means sacrificing performance.

Solution

  • You don't explain what "time decimal" format is. From your example, I'll guess that you mean decimal hours.

    A key function in SQL Server for date differences is datediff(). You can convert the time to seconds using a trick. Add the time to a date, then use datediff() to get the number of seconds after midnight. After that, the conversion to decimal hours is just arithmetic.

    Here is an example:

    select datediff(second,
                cast('2000-01-01' as datetime),
                cast('2000-01-01 ' + '00:00:59' as datetime)
               )/3600.0 as DecimalHours

    Note the use of the constant 3600.0. The decimal point is quite important, because SQL Server does integer division on integer inputs. So, 1/2 is 0, rather than 0.5.