Time duration in mm:ss

Question

Post reply

Time duration in mm:ss

rjames-833409

Valued Member

Points: 57
More actions
September 28, 2010 at 9:37 am

#228845

I'm currently using convert("duration",108) to display a duration of time in seconds (field is defined as a INT) as hh:mm:ss. It works fine but the customer wants to see the value in mm:ss - and I can't seem to find the right code.
Anyone have a quick fix off hand?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think it's just a combination of integer division and modulus:

/*--results

MinutesSeconds

2571

210

*/

--example where value is an integer of seconds

SELECT intSeconds / 60 as [Minutes],

intSeconds % 60 as [Seconds]

FROM

(SELECT 15421 AS intSeconds UNION ALL

SELECT 130 ) X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rjames-833409 Valued Member Points: 57 More actions · Answer 2

Not quite was I was looking for.

The convert function specifying 108 (as above) outputs the time duration as hh:mm:ss.

I'm looking for a function that would output mm:ss. Specifically drop the hh: part.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

--supply a date or getdate(), etc

declare @dtime datetime

select @dtime = '2010-07-04 10:25:21'

--pick out the parts you need

select cast(datepart(mi, @dtime) as varchar(2)) + ':' +

cast(datepart(ss, @dtime) as varchar(2))

Returns

25:21

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

LutzM SSC Guru Points: 107049 More actions · Answer 4

Using Perrys sample:

SELECT RIGHT(CONVERT(CHAR(8),@dtime,8),5)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]