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