how to convert seconds to time in sql

Question

how to convert seconds to time in sql

dastagiri16

Hall of Fame

Points: 3544
More actions
March 14, 2012 at 5:06 pm

#253054

hi,
Could you please give any formula for that to convert seconds to time...
Thanks,
Giri

Viewing 15 posts - 1 through 14 (of 14 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Yes sure ! ( A + B )2 = A2 + B2 + 2AB! 😀

On the other hand, convert to time = what?? time can be specified in hours, minutes, nano seconds, milli second et all.. what do u exactly want?

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 2

dastagiri16 (3/14/2012)
hi,
Could you please give any formula for that to convert seconds to time...
Thanks,
Giri

SELECT DATEADD(ss,someseconds,0)

That will give you a date/time and you probably won't like the looks of it but it's the first step. Will your seconds at up to less than 24 hours?

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What we are trying to say is that we need more information. To convert seconds to time we need to know the basis of the seconds. Is the number of seconds since midnight? From 1/1/1980?

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Assuming you mean time as something like HH:MM:SS, this may work:

DECLARE @seconds INT

SELECT @seconds = 500

SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +

RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +

RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

To get time as HH:MM:SS from seconds, you don't need to calculates hours, minutes and seconds, format and concatenate them separately. You can just use one of standard CONVERT options:

DECLARE @seconds INT

SELECT @seconds = 500

SELECT CONVERT(VARCHAR, DATEADD(second,@seconds,0),108)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Everyone is guessing at what you need. How about telling us what you really are trying to accomplish.

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

Sorry... post withdrawn... posted code for the wrong problem.

--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)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

Rats! Eugene posted:

To get time as HH:MM:SS from seconds, you don't need to calculates hours, minutes and seconds, format and concatenate them separately. You can just use one of standard CONVERT options:
DECLARE @seconds INT
SELECT @seconds = 500
SELECT CONVERT(VARCHAR, DATEADD(second,@seconds,0),108)

I was wondering if CONVERT had a feature for this but I was too lazy to look it up!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 9

hi,

Thanks for replying ...

It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .

Thanks,

Dastagiri.

Lowell SSC Guru Points: 323518 More actions · Answer 10

dastagiri16 (3/24/2012)
hi,
Thanks for replying ...
It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .
Thanks,
Dastagiri.

so...yo want elapsed time converted to years:months:hours:minutes:seconds, and not converted to datetime at all, then right?

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!

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

Lowell (3/24/2012)
dastagiri16 (3/24/2012)
hi,
Thanks for replying ...
It is not working in all cases suppose the seconds is more than 100 hours its showing wrong .
Thanks,
Dastagiri.
so...yo want elapsed time converted to years:months:hours:minutes:seconds, and not converted to datetime at all, then right?

Or do you just want hours, minutes, and seconds even if we go over 24 hours?

--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)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 12

Lowell,

could you please give me suggestion for..

if seconds=999999

then we can convert to time the result must be 277:46:39(hr:min:sec) ..

like that the earlier mail mentioned formula

DECLARE @seconds INT

SELECT @seconds = 500

SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +

RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +

RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

this is work for that...

Thanks,

Dastagiri

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 13

dastagiri16 (3/25/2012)
Lowell,
could you please give me suggestion for..
if seconds=999999
then we can convert to time the result must be 277:46:39(hr:min:sec) ..
like that the earlier mail mentioned formula
DECLARE @seconds INT
SELECT @seconds = 500
SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' +
RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +
RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]
this is work for that...
Thanks,
Dastagiri

Now that we finally know which format you want the result to be, the problem solution becomes very simple if we let SQL Server do most of the work.

DECLARE @Seconds INT;

SELECT @Seconds = 999999;

SELECT CAST(@Seconds/3600 AS VARCHAR(10))

+ RIGHT(CONVERT(CHAR(8),DATEADD(ss,@Seconds,0),108),6);

Result:

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

277:46:39

(1 row(s) affected)

--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)

dreamcodelife Grasshopper Points: 15 More actions · Answer 14

dwain.c - Thursday, March 15, 2012 5:11 AM
Assuming you mean time as something like HH:MM:SS, this may work:DECLARE @seconds INTSELECT @seconds = 500SELECT RIGHT('00'+CAST(@seconds/3600 AS VARCHAR), 2) + ':' + RIGHT('00'+CAST(@seconds/60 AS VARCHAR), 2) + ':' +RIGHT('00''00'+CAST(@seconds % 60 AS VARCHAR) , 2) AS [HH:MM:SS]

Will not work for negative dates and the datetime difference more than two digits in hour section