formatting N number of seconds to Days, hours, minutes and seconds

Question

Post reply

formatting N number of seconds to Days, hours, minutes and seconds

moe_mayne

SSCommitted

Points: 1933
More actions
May 16, 2007 at 5:01 pm

#172507

Good afternoon everyone i was wondering if anyone out there has done this... I have 11285847 seconds and i would like to format these seconds to come out in this kind of format "N days : N Hours : N Minutes" ... please help.. thanks

Moe C

Viewing 8 posts - 1 through 8 (of 8 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Try this. I'm sure there's a ton of ways to do it. Not feeling very clever right now.

declare

@Seconds int

Set

@Seconds = 11285847

declare

@Days int

declare @Hours int

declare @Minutes int

Set

@Days = @Seconds / 86400

set @Hours = (@Seconds % 86400) / 3600

set @Minutes = ((@Seconds % 86400) / 3600) / 60

set @Seconds = ((@Seconds % 86400) / 3600) % 60

select

ltrim(str(@Days)) + ' Days : ' + ltrim(str(@Hours)) + ' Hours : ' + ltrim(str(@Minutes)) + ' Minutes : ' + ltrim(str(@Seconds)) + ' Seconds'

Ray M SSC-Insane Points: 21093 More actions · Answer 2

That doesn't look right.

Try this

declare @Seconds int

declare @Date smalldatetime

Set

@Seconds = 11285847

declare

@Days int

select @Date = dateadd(ss, @Seconds, '01/01/1900')

select @Days = datediff(dd, '01/01/1900', @Date)

select ltrim(str(@Days)) + ' Days : ' + ltrim(str(datepart(hh, @Date))) +

' Hours : ' + ltrim(str(datepart(mi,@Date))) + ' Minutes : ' + ltrim(str(datepart

ss, @Date))) + ' Seconds'

Result

130 Days : 14 Hours : 57 Minutes : 0 Seconds

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Ooooowwww.... why on Earth do you want to do this? Even if it's just a report, if someone needs to ananlyze the data using any form of aggragation, they're pretty much screwed into having to undo the formatting you're doing. This type of thing should really be done in "the application" if you have one. If it's for a file you need to send someone, they'll pretty much be in the same boat.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

moe_mayne SSCommitted Points: 1933 More actions · Answer 4

Well i was hoping that i wouldnt need a SP and i can just do the formatting in the RDL... the customer wants to see something pretty and not 7200 seconds ...

Moe C

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Oh, you wanted it for reporting services.

You can basically do the same thing using VB Script.

=Int((Fields!Seconds.Value / 86400)) & " Days : " &

Int(((Fields!Seconds.Value Mod 86400) / 3600)) & " Hours : " &

Int((((Fields!Seconds.Value Mod 86400) Mod 3600) / 60)) & " Minutes : " &

(((Fields!Seconds.Value Mod 86400) Mod 3600) Mod 60) & " Seconds "

moe_mayne SSCommitted Points: 1933 More actions · Answer 6

This is great Ray.. thanks for all your help...

Moe C

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

I'll be the first to agree with making the end result pretty... I'm just not sure why they don't do that at the presentation layer... or, maybe that's the problem? They don't have a "presentation layer"?

Just curious... that's all...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)