Trouble converting from time to decimal time

Question

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.

score 0 · Accepted Answer · answered Mar 21 '14 at 20:23

0

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.

answered Mar 21 '14 at 20:23

Gordon Linoff

1,242,037
58
646
786

I noticed you're using a date and I do have a date column that corresponds to my time column (yes they're separate, both are varchars but the date is in the format of YYYY-MM-DD). When I meant "time decimal format", I pretty mucb meant decimal hours. – Cucko Oooo Mar 21 '14 at 20:45
@CuckoOooo . . . This uses an arbitrary date just to easily convert the time to seconds. It wouldn't make use of a date column unless you needed that for some reason. – Gordon Linoff Mar 21 '14 at 21:15
I got an update from my boss where he actually does want me to incorporate the date into my calculation. I abandoned my substring approach and decided to use yours with datediff since it's cleaner. After I did a few manual calculations for comparison purposes, the result was correct, so I accepted your answer. – Cucko Oooo Mar 24 '14 at 14:41

t-clausen.dk · Answer 2 · 2014-03-24T18:35:04.103

0

Here is a simple way to convert time to DecimalHours.

SELECT cast(cast('12.57:46' as datetime) as float) * 24

Result:

~12.963

edited Mar 24 '14 at 18:35

answered Mar 22 '14 at 19:08

t-clausen.dk

43,517
12
59
92

score 0 · Answer 3 · answered Mar 22 '14 at 21:22

You said,

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....

12.970 is wrong for an input of '12.57.46'. The problem is that you are using the SUBSTRING function incorrectly. The 3rd argument represents the number of characters, not the ending character position.

Take a look at this code:

Declare @Sample varchar(20)

Set @Sample = '12.57.46'

select  CAST(SUBSTRING(@Sample, 1, 2) AS FLOAT) +
        CAST(SUBSTRING(@Sample, 4, 5) AS FLOAT) / 60 + 
        CAST(SUBSTRING(@Sample, 7, 8) AS FLOAT) / 60 / 60,
        SUBSTRING(@Sample, 1, 2),
        SUBSTRING(@Sample, 4, 5),
        SUBSTRING(@Sample, 7, 8),
        CAST(SUBSTRING(@Sample, 1, 2) AS FLOAT) +
        CAST(SUBSTRING(@Sample, 4, 2) AS FLOAT) / 60 + 
        CAST(SUBSTRING(@Sample, 7, 2) AS FLOAT) / 60 / 60

Notice that the minutes is coming out as 57.46 because you are asking for 5 characters. The seconds are coming out correctly because eventhough you are asking for 8 characters, there are only 2 characters left in the string so only those 2 characters are returned.

BTW, I would solve this problem the same way that Gordon did, except I would remove the date stuff so it would look like this:

Select DateDiff(Second, 
                0, 
                Convert(DateTime, Replace([OASTIM], '.',':'))) / 3600.0

@G Mastros Ah, I see where I goofed, thanks! BTW, I got an update from my boss where he wants my to incorporate the date into my calculation. For example, if the time is the same but the dates are different, then the result shouldn't be 0, it should be the number of decimal hours between those 2 dates. I think I got it to work using the datediff function as it seems cleaner than using substrings. — Cucko Oooo, Mar 24 '14 at 14:38

Trouble converting from time to decimal time

3 Answers3