changing format of seconds into dd:mm:ss

Question

0.00/5 (No votes)

See more:

Following Query returns me Number difference in format of "HH:MM:SS"

SQL

select convert(char(8), dateadd(ss, DATEDIFF(ss,'2013-04-16 05:00:00' , '2013-04-16 20:00:10'), cast(0 as DateTime)) , 108)

output of above query is " 15:00:10 " which is correct but if i change query as below

SQL

select convert(char(8), dateadd(ss, DATEDIFF(ss,'2013-04-16 05:00:00' , '2013-04-18 20:00:10'), cast(0 as DateTime)) , 108)

I have changes date in above query

output of above query is " 15:00:10".

My question : " Is there a way to get output in "dd:hh:ss" (days : hours : min) format ?

Posted 12-May-13 19:58pm

deepakdynamite

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Code-Hunt · Answer 1 · 2013-05-12T20:12:00

Solution 1

SQL

Declare @Seconds as int
Declare @SubtractDate as datetime
--Enter Number of Seconds here
Set @Seconds=9974501
Set @SubtractDate=DateAdd(s,@Seconds,getdate()) - Getdate()
 
Select Convert(varchar(10),DateDiff(day,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +

Convert(varchar(10),DatePart(mi,@SubtractDate))
+ ' Minute(s) ' +
Convert(varchar(10),DatePart(ss,@SubtractDate))
+ ' Second(s) ' AS [Result]
--OUTPUT

Run this script in sql..hope u will get your solution out of it...

Posted 12-May-13 20:12pm

Code-Hunt

Comments

deepakdynamite 13-May-13 3:02am

Any idea if this can be done in single statement ?

[no name] 13-May-13 3:20am

If my answer helped you then don't forget to accept the solution..it will help for future reference..

[no name] 13-May-13 3:20am

I am not sure because u have to break the time in to a user define format..other formats are easy to go for..e.g. http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
in this link u will get most of the user defined formats..so single line may be difficult..
Note:i am not telling not possible..

deepakdynamite · Answer 2 · 2013-05-13T00:43:00

Solution 2

select CAST(DATEDIFF(dd,'2013-04-16 05:00:00' , '2013-04-19 20:00:10') as varchar) +':'+convert(char(5), dateadd(ss, DATEDIFF(ss,'2013-04-16 05:00:00' , '2013-04-16 20:00:10'), cast(0 as DateTime)) , 108)

Posted 13-May-13 0:43am

deepakdynamite