Calculate DateDiff in SQL in Days:Hours:Mins:Seconds format

sql datediff
user1423779 picture user1423779 · May 29, 2012 · Viewed 48k times · Source

I am currently working an SQL script to calculate the difference between two dates which would give me the result in DD:HH:MI:SEC format. Example: Date 1: 7/30/12 4:00 PM Date 2: 5/4/12 10:31 AM

And the result should be 87:05:29:00

Can you kindly help with the script for this? Regards, Arjun

Answer

Arion picture Arion · May 29, 2012

If you are using sql-server then you can do this:

declare @x int, 
        @dt1 smalldatetime = '1996-03-25 03:24:16', 
        @dt2 smalldatetime = getdate()

set @x = datediff (s, @dt1, @dt2)


SELECT convert(varchar, @x / (60 * 60 * 24)) + ':'
+ convert(varchar, dateadd(s, @x, convert(datetime2, '0001-01-01')), 108)

Reference here

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
How to compare two dates to find time difference in SQL Server 2005, date manipulation

I have two columns: job_start job_end 2011-11-02 12:20:37.247 2011-11-02 13:35:14.613 How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended? I …

Read More
SQL Server Group By Month

I have a table which has this schema ItemID UserID Year IsPaid PaymentDate Amount 1 1 2009 0 2009-11-01 300 2 1 2009 0 2009-12-01 342 3 1 2010 0 2010-01-01 243 4 1 2010 0 2010-02-01 2543 5 1 2010 0 2010-03-01 475 I'm trying to get a query working which shows the totals for each month. So far …

Read More