CODEWITHSUNDEEP
sqlsql-server-2005
user1982736
user1982736

Reputation:

How to convert minutes to HH:MM format

I have query, which has to give the minutes as HH:MM format.

SELECT (cast(SUM(convert(int,total)) / 60  as varchar(5))+':' + 
        RIGHT('0' +cast(sum(convert(int,total) % 60) as varchar(2)),2)) 
FROM (SELECT  CASE(clnt_cntLngth)
          WHEN 0 THEN '15'
          WHEN 1 THEN '30'
          WHEN 2 THEN '60'
          WHEN 3 THEN '120'
          WHEN 4 THEN '300'
          ELSE '0' END as total  from dbo.clientInfo) as tbl`

which is referred from here. It is giving good results for some values and not for some values. I don't know why. for example See the bellow results Images. This is giving write ans enter image description here

ans this is giving wrong output enter image description here what makes difference between those. Can anyone suggest any more ways to do this for exact output. I did not change anything. 1st one is write answer ans second one has to be 1:00 but it is showing 1:60.

Upvotes: 3

Views: 1477

Answers (1)

Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

CREATE FUNCTION [dbo].[IntToMinutes]
(
    @m int
)
RETURNS nvarchar(20)
AS  
BEGIN

    DECLARE @c datetime
    DECLARE @c1 datetime
    SELECT @c =dateadd(mi,0,'00:00')              
    select @c1 = dateadd(mi,@m,'00:00')         

     return CONVERT(varchar(10),DATEDIFF(hh, @c, @c1) ) + ':' +
     CONVERT(varchar(10),DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @c, @c1),@c),@c1) )
    end

Upvotes: 1

Related Questions