T-SQL: udf_DurationHourMinSec() – Duration between start date and end date

I built a function for reporting purposes that shows the duration in hh:mm:ss between two datetime columns. It can be used in a reporting tool to show the data in a log.

The max difference in DATEDIFF() is 68 years. Quick and extended calculation (every year is a leap year):
68 years * 366 days * 24 hours = 597,312 hours. The returned datatype is VARCHAR(12) (two chars for seconds, two chars for minutes, two chars for delimiters and 6 chars for hours)

Create Database.

Create Function.

Test Function.

Test udf_DurationHourMinSec()

Paste the result in Excel 2013 to verify the result:

Verify udf_DurationHourMinSec() in Excel 2013

595727 – 595703 = 24 hours
Just for fun I will say that there is one mischievous day in a leap year ;-)

Delete Database.

Keep it simple :-)