Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

How to Display hours minutes seconds between two dates

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 



This post first appeared on SQLSERVER-DBA.com, please read the originial post: here

Share the post

How to Display hours minutes seconds between two dates

×

Subscribe to Sqlserver-dba.com

Get updates delivered right to your inbox!

Thank you for your subscription

×