CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

convert minute into hours sql server

I need to convert minutes into hours in sql server.

I used following logic to do it.

 CAST(REPLACE(LEFT(CONVERT(varchar(10), DATEADD(MINUTE, 19.80 *100, ''), 114),5),':','.')    AS Decimal(5,2)) AS tpschedhours

My expected Output is 33 hours (1980 minutes in hours)

But I got output as 9 hours. I have found that, the issue occurs because DATEADD(MINUTE,1980, '') returns ouptut as 1900-01-02 09:00:00.000 (One day + 9 hours). But I need the Output as Hours value i.e 33 hours

Thanks for the help

Upvotes: 0

Views: 2476

Answers (3)

bmsqldev
bmsqldev

Reputation: 2735

I Got the solution from the answers.

SELECT CAST((CAST(((2.72) *100)AS INT) / 60 )+ (CAST((2.72 *100)AS INT) % 60) / 100.0 AS DECIMAL(5,2)).

thanks tinka and Stanislovas Kalašnikovas

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can try in following:

DECLARE @time INT = 1980

SELECT LEFT(CONVERT(VARCHAR(10), DATEADD(MINUTE, @time / 60 + (@time % 60), ''),114),5)

Upvotes: 1

Hüseyin Burak Karadag
Hüseyin Burak Karadag

Reputation: 1888

SELECT CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @StartDate, @EndDate), 0), 114)

Upvotes: 1

Related Questions