locked
Need help with average format RRS feed

  • Question

  • Hi All

    I need some guidance on getting the average in HH:MM format.

    I have the "pending avergaetime" (please do not take the name literally). as datediff(min,createddate,getdate()).

    I have an outer query that sums up the "pending average time" and divide it with the sum of the "pending count" please see the below image my outer query is calculating the average as (207+171+156+21+22)/5=115.4

    Want i need help with is how do i show the 115.4 as hours and minutes. Please advice.

    Thanks

    Wednesday, March 20, 2019 5:08 PM

Answers

  • Hi LordofthePlains,

    Here are another two methods for your reference.

    SELECT RIGHT('0'+CAST((207+171+156+21+22)/5/60 AS VARCHAR(30)),2)+':'+RIGHT('0'+CAST((207+171+156+21+22)/5%60 AS VARCHAR(30)),2) as [HH:MM]
    
    --OR
    SELECT FORMAT(DATEADD(MI,(207+171+156+21+22)/5,CAST('00:00:00' AS TIME)),N'hh\:mm') as [HH:MM]

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 6:12 AM

All replies

  • SELECT Convert(varchar(5),(dateadd(minute, 165,'1900-01-01')), 108) as [hh:mm]
    Wednesday, March 20, 2019 6:04 PM
  • Hi LordofthePlains,

    Here are another two methods for your reference.

    SELECT RIGHT('0'+CAST((207+171+156+21+22)/5/60 AS VARCHAR(30)),2)+':'+RIGHT('0'+CAST((207+171+156+21+22)/5%60 AS VARCHAR(30)),2) as [HH:MM]
    
    --OR
    SELECT FORMAT(DATEADD(MI,(207+171+156+21+22)/5,CAST('00:00:00' AS TIME)),N'hh\:mm') as [HH:MM]

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 6:12 AM
  • Thanks Will and Jingyang Li
    Friday, March 22, 2019 4:29 PM