Solved

# Converting minutes to hh mm

Posted on 2008-10-25
Medium Priority
409 Views
Hi

Currently I am using  CAST(flexcel.unformatted_remaining / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(flexcel.unformatted_remaining % 60 AS varchar(3)), 2) to convert minutes to HH:MM. this works well when the Number of minutes is positive i.e 01:10  , however if the number is negative i.e -10:05 problems arise with the display I have tried changing  varchar(3)), 2 to varchar(3)), 3 and different combinations
but when I get the negative right the posetive show wrong and visa  versa
0
Question by:Camnoc
• 3
• 3

LVL 37

Expert Comment

ID: 22805249
well, when you get the number of minutes (i assume you use datediff) apply the abs function to it, this way it will always be positive
0

Author Comment

ID: 22805501
What is the abs function. I am using datediff to calculate the .time to minutes. Please understand I am new to sql.
0

LVL 61

Expert Comment

ID: 22805995
This is what momi is referring too:
http://msdn.microsoft.com/en-us/library/ms189800.aspx

SELECT ABS(-200) ;
==> 200
0

Author Comment

ID: 22806743
This seems to give a result in decimal. I am trying to display -15 min as -00:15 or 15 as 00:15 ??
0

LVL 61

Expert Comment

ID: 22807461
Was just giving you an example of what was being suggested, you will have to same formula you had above with absolute inserted.

CAST(ABS(flexcel.unformatted_remaining) / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(ABS(flexcel.unformatted_remaining) % 60 AS varchar(3)), 2)
0

LVL 61

Accepted Solution

Kevin Cross earned 2000 total points
ID: 22807472
If we are misunderstanding and you are trying to show negatives, then you can do this instead.

``````CAST(flexcel.unformatted_remaining / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(ABS(flexcel.unformatted_remaining) % 60 AS varchar(2)), 2)
``````
0

Author Closing Comment

ID: 31510011
THANK YOU
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month5 days, 12 hours left to enroll

#### 588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.