CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dharmendra Kumar Singh
Dharmendra Kumar Singh

Reputation: 2991

Convert Decimal values(hours) to Appropriate Hours in sql server 2008

I have a column called TimeSpent of type Float.

It contains values (i.e. 2.50, 1.25, 3.75, 5.60 )

I need to convert these hour values to appropriate hour values like (2:30, 1:15, 3:45 ,5:36) etc)

How can this be done?

Upvotes: 0

Views: 3079

Answers (3)

fancyPants
fancyPants

Reputation: 51868

Assuming you have created already a column with datatype time, you update your table by concatenating the left of TimeSpent till the dot and the part right of the dot multiplied by 60.

SQL Server 2012:

UPDATE yourTable SET
newTimeColumn = CONCAT(CAST(TimeSpentFloat AS INT), ':', (TimeSpentFloat - CAST(TimeSpentFloat AS INT)) * 60);

SQL Server 2008 and lower:

SELECT 
CAST(CAST(2.75 AS INT) AS CHAR) + ':' + CAST((2.75 - CAST(2.75 AS INT)) * 60 AS CHAR);

See it working in an sqlfiddle.

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try this Query

select 
time_decimal
,cast(cast(cast(time_decimal as int) as varchar)+
':'+cast(cast((time_decimal - cast(time_decimal as int)) * 60 as int) as varchar)+
':'+cast(cast(((time_decimal - cast(time_decimal as int)) * 60-
    cast((time_decimal - cast(time_decimal as int)) * 60 as int)) * 60 as int) as varchar) as time) as real_time
from time1

SQL FIDDLE

Upvotes: 1

ssarabando
ssarabando

Reputation: 3517

Just do the math: the hour you can get by casting to int, for example. To get the minutes, multiply the decimal part by 60, etc. A quick and dirty way to do it in a single query could be:

declare @d float
set @d = 1.54
select cast(@d as int) h,
       cast((@d - cast(@d as int)) * 60 as int) m,
       cast(((@d - cast(@d as int)) * 60
            - cast((@d - cast(@d as int)) * 60 as int)) * 60 as int) s

Upvotes: 0

Related Questions