# how to convert seconds to time in sql

• hi,

Could you please give any formula for that to convert seconds to time...

Thanks,

Giri

• 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?

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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?

• 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?

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

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

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

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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?

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

• hi,

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

Thanks,

Dastagiri.

• dastagiri16 (3/24/2012)

hi,

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!

• Lowell (3/24/2012)

dastagiri16 (3/24/2012)

hi,

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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]`