CODEWITHSUNDEEP
sql-servert-sql
LosTorros
LosTorros

Reputation: 11

converting int to hh:mm format

I have separate integers for hours and minutes and i need to find a way to get the total number of hours followed by minutes preferably in a HH:MM format. The issue that i'm facing is when the minutes are less than ten there is no leading zero and i am doing this for reporting reasons and so would love to be able to do something like

Total Hours worked

102:06 to represent 102 hours and 6 minutes

DECLARE @hours INT  = 102
declare @minutes int = 6

SELECT
 CONCAT(CAST (SUM((@hours*60)+@minutes)/60 AS VARCHAR(5)) , ':' , CAST (SUM((@hours*60)+@minutes)%60 AS VARCHAR(2)))

Upvotes: 0

Views: 402

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

Just another similar option using format()

Example

DECLARE @hours INT  = 102
declare @minutes int = 6

Select concat(@hours,format(@minutes,':00'))

Returns

102:06

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

In SQL Server, you can do:

select concat(hours, ':',
              right('00' + minutes, 2)
             )

Another method would be:

select concat(hours, ':',
              right(convert(varchar(255), 100 + minutes), 2)
             )

Upvotes: 1

Related Questions