Quick Fix: How to display hours minutes seconds between two dates

09 July,2020 by Jack Vamvas

Question: I need to present the difference between two dates  - returned by GETDATE() , which are using the datetime data type, as hours , minutes, seconds i.e hh:mm:ss  , not as columns - but as one string . For example , if its 1 hr, 10 min, 15 seconds - than it should be : 01:10:15  . 

How can this be done?

Answer: There's a few different ways you manage this result . For example - you could use the DATEDIFF method - comparing two different dates and returning the datepart defined between the two dates.

In your case , you are using the DATETIME data type and want to return in the format hh:mm:ss   - between the two dates. Here is a simple way to pass getdate() into a variable , calculate the difference between the two values - and cast the dration difference as the time data type. The time data type is useful in this situation as it returns the format : hh:mm:ss[.nnnnnnn]

 

 

DECLARE @before DATETIME, @after DATETIME,@duration varchar(20);
SET @before = getdate();
--do something here 
SET @after = getdate();
SET @duration = (Select CAST((@after-@before) as time(0)) '[hh:mm:ss]')

PRINT @duration;


Read more on SQL dates

SQL 2012 datetime rounding issue

How to use ISO-8601 for date and datetime 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Quick Fix: How to display hours minutes seconds between two dates


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer