Solved

Converting minutes to hh mm

Posted on 2008-10-25
7
393 Views
Last Modified: 2012-05-05
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
Comment
Question by:Camnoc
  • 3
  • 3
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
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

by:Camnoc
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 59

Expert Comment

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

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Camnoc
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 59

Expert Comment

by:Kevin Cross
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 59

Accepted Solution

by:
Kevin Cross earned 500 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)

Open in new window

0
 

Author Closing Comment

by:Camnoc
ID: 31510011
THANK YOU
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now