It's about time...

It's been a while since last post, and it is not as frequent as I wish, but it's about time or more lack of time.

First I want to celebrate Microsoft SQL Server for yet another amazing release. Now it is Microsoft SQL Server 2019, there are so many new features I guess I must start writing some about everything I love in this product. And that is almost a promise, I'll try to do more posts, and feature all this goodness.

Now over to todays topic, time. I found a question on the SQL Server 2008 - 2019 Facebook group, https://www.facebook.com/groups/7193059737/10157742825254738. And it was from Islam Selim:

Hi all
I need to create time column in view calculate differeance between two other datetime columns.. Plz help

And my response was this:

CREATE FUNCTION dbo.fn_TimeBetween (
 @start datetime,
 @end datetime,
 @days int  = 0 -- 0 for 00H,00M,00S (Default), 1 for 00D,00H,00M,00S
 )
RETURNS varchar(max)
AS
BEGIN
DECLARE
 @distance int = DATEDIFF(second, @start, @end),
 @result varchar(max);
DECLARE
 @seconds int = @distance % 60
SET
 @distance = (@distance - @seconds) / 60
DECLARE
 @minutes int = @distance % 60
SET
 @distance = (@distance - @minutes) / 60
DECLARE
 @hours int = @distance % 24
SET
 @distance = (@distance - @hours) / 24
IF @days = 0
 SET @result = CAST(@distance * 24 + @hours AS varchar(max)) + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    +'S'
ELSE
 SET @result = CAST(@distance AS varchar(max))     + 'D,' +
 RIGHT('0' +  CAST(@hours AS varchar(max)), 2)    + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'S'
RETURN @result
END