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

dbo.digits() - a sample In-line TVF using CTE

I found a question in one of the online forums where a user wanted to generate digits, like for an counter, but control how many digits and a range of allowed digits.

ex. I want 2 digits, and only the digits between 3 and 5 giving me a result like this.

digits
  33
  34
  35
  43
  44
  45
  53
  54
  55

I came up with this solution...

CREATE FUNCTION dbo.digits(
@len int,
@min int,
@max int
)
RETURNS TABLE
AS
RETURN
WITH
digits (digit) AS (
SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL
SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL
SELECT '9'
),
selection AS (
SELECT
digit
FROM digits
WHERE
CAST(digit AS int) BETWEEN @min AND @max
),
result AS (
SELECT
0 AS position,
CAST(digit as varchar(max)) digit
FROM selection
UNION ALL
SELECT
r.position + 1,
CONCAT(s.digit,r.digit)
FROM result r
CROSS JOIN selection s
WHERE r.position < @len
)
SELECT
digit AS digits
FROM result
WHERE position = @len - 1

You then execute the TVF with this line:
SELECT * FROM dbo.digits(2, 3, 5) ORDER BY 1

Changes

This is another not about SQL post. As of march 1st of 2019 I'm working for Axians IoT Operations and are adding value on top of their amazing solution microServiceBus, which connects any device to any IOT hub. Or most major ones at least.

For me this is about changing life, and adding a balance between work and life. In todays' ever changing world it is crucial to reflect over life. I did for sure and am truly grateful for this opportunity.

I will get back to blogging, answering questions at forums, and interact with the community as well as product groups. I will also start blueprinting the book I've been thinking about for years.

More to come...

Yours truly,
Mattias