SQLTeam.com | Weblogs | Forums

Converting an hour and minute to an integer or numeric value..how to do it?


#1

I have a field that is a median_call_time for phone conversations.

Instead of having it display as 00:02:35

I'd like it to display as a numeric value like 2.35.

Any thoughts?


#2

See if this gives you any ideas

declare @a time = '0:02:35';
select @a, DATEPART(Minute,@a), DATEPART(Second,@a);

#3
SELECT 
    time_value,
    CAST(DATEPART(HOUR, time_value) * 60 + DATEPART(MINUTE, time_value) AS varchar(4)) + '.' + 
    RIGHT('0' + CAST(DATEPART(SECOND, time_value) AS varchar(2)), 2) AS mins_decimal
FROM (
    SELECT CAST('00:02:35' AS time) AS time_value UNION ALL
    SELECT '00:00:02' UNION ALL
    SELECT '01:07:16'
) AS test_data