CODEWITHSUNDEEP
sqlsql-server-2008
Vignesh Nagarajan
Vignesh Nagarajan

Reputation: 37

Convert minutes in Numeric format to Hours and minutes format

I Have Minutes as 1064 in a column called 'Efforts_in_minutes' and I need the Output in the format of HH:MM (i.e) 17:44. I have tried the below Query 

SELECT Cast(Round(Total_Effort_in_Minutes / 60, 0, 2) AS VARCHAR) 
        + ':' 
        + Cast(Total_Effort_in_Minutes % 60 AS VARCHAR(2))
FROM   PPS

I got the output as 17.000000:44 but What i Need is 17:44

Please advice how to achieve that.

Upvotes: 1

Views: 303

Answers (4)

Stavr00
Stavr00

Reputation: 3314

SELECT DATEADD(MI,Total_Effort_in_Minutes,TIMEFROMPARTS(0,0,0,0,0))
FROM   PPS

Best practice: The client should decide how to represent the time based on the locale, to allow for variants such as 24H clock vs AM/PM.

Upvotes: 1

trincot
trincot

Reputation: 349956

The use of the Round function is unnecessary. Just do integer division:

select CAST(CAST(Total_Effort_in_Minutes AS INT) / 60 AS VARCHAR)
    + ':' + CAST(Total_Effort_in_Minutes  % 60 AS VARCHAR(2) )

If your column Total_Effort_in_Minutes already has an integer data type, then you can simplify to:

select CAST(Total_Effort_in_Minutes / 60 AS VARCHAR)
    + ':' + CAST(Total_Effort_in_Minutes  % 60 AS VARCHAR(2) )

If you need the minute part to be left-padded with zero to get at least 2 digits, then:

select RIGHT('0' + CAST(CAST(Total_Effort_in_Minutes AS INT) / 60 AS VARCHAR), 2)
    + ':' + CAST(Total_Effort_in_Minutes  % 60 AS VARCHAR(2) )

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Just for fun:

SELECT CONVERT(CHAR(5), DATEADD(mi, 1064, 0), 8)

Upvotes: 1

ElijahJoel
ElijahJoel

Reputation: 11

Please see this link: https://stackoverflow.com/a/19887500/6298495

Creating an inline function is a good solution provided in the link.

Upvotes: 0

Related Questions