CODEWITHSUNDEEP
sql-server-2008
Somashekhar
Somashekhar

Reputation: 513

How to convert decimal time to time in SQL Server 2008

I have decimal time and I am not able to convert into time. 

DECLARE @hours decimal(5,3) 
SELECT @hours = 20.30 //20 Hrs and 30 mins

I need the output in below format

 Hours  20.5 AS Time (data type)

Below is my code

  select cast(hours /24 as datetime)

this code gives output as 20.18 Hrs. However I need 20.5 hrs

Upvotes: 1

Views: 8713

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

Since the decimal part does not really represent the decimal portion of the hour, you need to chop if off and process it separately.

In SQL Server 2012:

select TIMEFROMPARTS ( floor(hours), 100*(hours - floor(hours)), 0, 0, 0 )

Prior to SQL 2012:

select cast(cast(floor(hours) as char(2))+':'+cast(floor(100*(hours - floor(hours))) as char(2)) as time)

Upvotes: 2

Related Questions