How to convert number of minutes to hh mm format in TSQL

Question

I have a select query that has DURATION column to calculate number of Minutes   I want to convert those minutes to hh mm format   Duration has values like 60  120 150  For example   60 becomes     01 00 hours  120 becomes    02 00 hours  150 becomes  02 30 hours   Also  this is how I retrieve DURATION  Minutes   DATEDIFF minute  FirstDate LastDate  as  Duration  Minutes

User · Answer

This function is to convert duration in minutes to readable hours and minutes format. i.e 2h30m. It eliminates the hours if the duration is less than one hour, and shows only the hours if the duration in hours with no extra minutes.

CREATE FUNCTION [dbo].[MinutesToDuration]
(
    @minutes int 
)
RETURNS nvarchar(30)

AS
BEGIN
declare @hours  nvarchar(20)

SET @hours = 
    CASE WHEN @minutes >= 60 THEN
        (SELECT CAST((@minutes / 60) AS VARCHAR(2)) + 'h' +  
                CASE WHEN (@minutes % 60) > 0 THEN
                    CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
                ELSE
                    ''
                END)
    ELSE 
        CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
    END

return @hours
END

To use this function :

SELECT dbo.MinutesToDuration(23)

Results: 23m

SELECT dbo.MinutesToDuration(120)

Results: 2h

SELECT dbo.MinutesToDuration(147)

Results: 2h27m

Hope this helps!

User · Answer

This seems to work for me  SELECT FORMAT  mins   60   100    mins   60     0

User · Answer

In case someone is interested in getting results as  60 becomes 01 00 hours  120 becomes 02 00 hours  150 becomes 02 30 hours  this function might help       create FUNCTION  dbo   MinutesToHHMM         minutes int    RETURNS varchar 30  AS  BEGIN declare  h int         set  h   minutes   60         declare  mins varchar 2          set  mins  iif  minutes 60 lt 10 concat  0  cast   minutes   60  as varchar 2    cast   minutes   60  as varchar 2    return iif  h  lt 10  concat  0   cast  h as varchar 5        mins           concat cast  h as varchar 5        mins    end

User · Answer

declare function dbo minutes2hours        minutes int   RETURNS varchar 10  as begin     return format dateadd minute  minutes  00 00 00    N HH  mm   FR-fr   end

User · Answer

select convert varchar 5  dateadd mi DATEDIFF minute  FirstDate LastDate   00 00   114

User · Answer

I would do the following  copy-paste the whole stuff below into immediate window   query window and execute   DECLARE  foo int DECLARE  unclefoo smalldatetime SET  foo   DATEDIFF minute  CAST  2013 01 01 00 00 00  AS datetime  CAST  2013 01 01 00 03 59  AS datetime   -- AS  Duration  Minutes    SET  unclefoo   DATEADD minute   foo   2000 01 01    SELECT CAST  unclefoo AS time     foo stores the value you generate in your question  The  trick  comes by then  we create a smalldatetime variable  in my case it s yyyy mm dd format  and increment it with your int value  then display  or store if you want  the time part only

User · Answer

I m not sure these are the best options but they ll definitely get the job done   declare  durations table       Duration int    Insert into  durations Duration  values 60   80   90   150   180   1000   --Option 1 - Manually concatenate the values together select right  0    convert varchar Duration   60  2          right  0    convert varchar Duration   60  2  from  Durations  --Option 2 - Make use of the time variable available since SQL Server 2008 select left convert time DATEADD minute Duration 0   5  from  durations  GO

User · Answer

For those who need convert minutes to time with more than 24h format   DECLARE  minutes int   7830 SELECT CAST  minutes   60 AS VARCHAR 8           FORMAT  minutes   60   D2   AS  Time    Result   130 30

User · Answer

Thanks to A Ghazal  just what I needed   Here s a slightly cleaned up version of his her  answer   create FUNCTION  dbo   fnMinutesToDuration         minutes int    RETURNS nvarchar 30   -- Based on http   stackoverflow com questions 17733616 how-to-convert-number-of-minutes-to-hhmm-format-in-tsql  AS  BEGIN  return rtrim isnull cast nullif   minutes   60                                    0                                  as varchar                              h                                                              isnull CAST nullif   minutes   60                                    0                                   AS VARCHAR 2                                m                                                                end

User · Answer

How to get the First and Last Record time different in sql server            Select EmployeeId EmployeeName AttendenceDate MIN Intime  as Intime  MAX OutTime  as OutTime  DATEDIFF MINUTE  MIN Intime   MAX OutTime   as TotalWorkingHours FROM ViewAttendenceReport WHERE AttendenceDate  gt   1 20 2020 12 00 00 AM  AND AttendenceDate  lt   1 20 2020 23 59 59 PM   GROUP BY EmployeeId EmployeeName AttendenceDate

User · Answer

You can convert the duration to a date and then format it   DECLARE      FirstDate datetime       LastDate datetime  SELECT      FirstDate    2000-01-01 09 00 00        LastDate    2000-01-01 11 30 00   SELECT CONVERT varchar 12           DATEADD minute  DATEDIFF minute   FirstDate   LastDate   0   114       Results  02 30 00 000      For less precision  modify the size of the varchar    SELECT CONVERT varchar 5           DATEADD minute  DATEDIFF minute   FirstDate   LastDate   0   114       Results  02 30

User · Answer

DECLARE  Duration int  SET  Duration  12540    for example big hour amount in minutes - gt  209h     SELECT CAST  CAST   Duration  AS int    60 AS varchar           right  0    CAST CAST   Duration  AS int    60 AS varchar 2   2      you will get hours and minutes divided by

[sql-server] How to convert number of minutes to hh:mm format in TSQL?

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to type-conversion

Examples related to datediff