How to get time in hh:mm format from minutes

Question

0.00/5 (No votes)

See more:

I am using dbo.MinutesToDuration function but its returning
2:7
But I want like that
02:07

Thanks in advance !

What I have tried:

ALTER 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(10)) + ':' + --+ 'h' +  
                CASE WHEN (@minutes % 60) > 0 THEN
                   CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm'
                ELSE
                    ''
                END)
    ELSE 
        CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm'
    END
return @hours

END

SELECT dbo.MinutesToDuration(127) AS TIME

Posted 12-Jul-17 20:57pm

arvindcis

Updated 12-Jul-17 22:22pm

v2

Add a Solution

Comments

OriginalGriff 13-Jul-17 4:26am

Solution updated.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-07-12T21:12:00

We don't know what your MinutesTODuration function does - it's not a standard SQL function, so you need to start looking at that.

However, when I use a TIME variable, I get what I expect:

SQL

DECLARE @st TIME(0) = '02:07'
SELECT @st AS TIME

Gives:

TIME
02:07:00

As does

SQL

DECLARE @st TIME(0) = '2:7'
SELECT @st AS TIME

So you need to look at what you are doing with your presentation layer that is formatting the TIME value.

[EDIT]
OK, so you showed us your function - which doesn't return a TIME, it returns a string ... which you specifically fill with non-leading-zero values ...

Assuming you use SQL 2012 or later:

SET @hours = 
    CASE WHEN @minutes >= 60 THEN
	        (SELECT FORMAT(@minutes / 60, 'd2') + 
                CASE WHEN (@minutes % 60) > 0 THEN
                   ':' + FORMAT(@minutes % 60, 'd2')
                END)
    ELSE 
        FORMAT(@minutes % 60, 'd2')
    END

If you use an earlier version then the FORMAT needs to be replaced with:

SELECT RIGHT('00' + CAST(@minutes / 60 AS NVARCHAR(5)), 2)

And similar for the remainder.

[/EDIT]

BinnyVishwant · Accepted Answer · 2017-07-12T22:22:00

However, i have tried this by adding '0' and not changing the code logic.
Please let me know,if this would simplifies your problem!

declare
 
    @minutes int=301

 
--RETURNS nvarchar(30)
--AS
BEGIN
declare @hours  nvarchar(20)
SET @hours = 
    CASE WHEN @minutes >= 60 THEN
	        (SELECT RIGHT ('0'+ CAST((@minutes / 60) AS VARCHAR(10)),2) + ':' + --+ 'h' +  
                CASE WHEN (@minutes % 60) > 0 THEN
                   RIGHT('0' + CAST((@minutes % 60) AS VARCHAR(10)),2) --+ 'm'
                ELSE
                    '00'
                END)
    ELSE 
        CAST((@minutes % 60) AS VARCHAR(10)) --+ 'm'
    END

PS: If you need to increase the hours range more than 100 then simply add 3 instead of 2

VARCHAR(10)),2)

How to get time in hh:mm format from minutes

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get time in hh:mm format from minutes

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us