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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |