Convert ISO 8601 duration to decimal time SQL values such as PT7H30M or PT8H0M

Question

I need to convert a field that has ISO 8601 duration in a varchar field to a decimal value representing that duration in hours.

How would I do a SELECT using the following data so results come back with the row values of 8.0 (PT8H0M), 7.5 (PT7H30M) and 1.0 (PT1H0M) for the duration field?

CREATE TABLE [dbo].[timetracking](
    [qbsql_id] [int] IDENTITY(1,1) NOT NULL,
    [username_id] [int] NULL,
    [TxnDate] [datetime2](0) NULL,
    [Duration] [varchar](50) NULL,
PRIMARY KEY CLUSTERED ([qbsql_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[timetracking] ON 
GO

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (1, 1, CAST(N'2018-02-02T00:00:00.0000000' AS DateTime2), N'PT8H0M')

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (2, 2, CAST(N'2018-02-01T00:00:00.0000000' AS DateTime2), N'PT7H30M')

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (3, 1, CAST(N'2018-02-01T00:00:00.0000000' AS DateTime2), N'PT1H0M')
GO
SET IDENTITY_INSERT [dbo].[timetracking] OFF

Answer 1

There is no built-in function I'm afraid. I just wrote one, which is fully inlineable ad-hoc SQL - but it won't be fast...

You can try this:

CREATE FUNCTION dbo.ConvertISO8601Periode2Seconds(@periode VARCHAR(100))
RETURNS TABLE
AS
RETURN
WITH Variables AS
(
    SELECT CASE WHEN CHARINDEX('T',@Periode)>0 THEN CHARINDEX('M',@periode,CHARINDEX('T',@Periode))-1 ELSE -1 END AS posMinute
          ,REPLACE(SUBSTRING(@periode,2,LEN(@periode)),'T','0') AS Original
)
,SwitchMinute AS
(
    SELECT CASE WHEN posMinute>0 THEN STUFF(Original,posMinute,1,'X') ELSE Original END AS WorkWith
    FROM Variables
)  
,recCTE AS
(      
    SELECT CAST(0 AS FLOAT) AS Seconds
          ,1 AS StartPos
          ,2 AS nextPos
          ,WorkWith 
    FROM SwitchMinute

    UNION ALL

    SELECT CASE SUBSTRING(r.WorkWith,r.nextPos,1) 
                WHEN 'Y' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 365 * 24 * 60 * 60
                WHEN 'M' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 30 * 24 * 60 * 60
                WHEN 'W' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 7 * 24 * 60 * 60
                WHEN 'D' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 24 * 60 * 60
                WHEN 'H' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 60 * 60
                WHEN 'X' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 60
                WHEN 'S' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 1
           ELSE 0
           END + r.Seconds
           ,CASE WHEN SUBSTRING(r.WorkWith,r.nextPos,1) IN('Y','M','W','D','H','X','S') THEN r.nextPos+1 ELSE r.StartPos END 
           ,r.nextPos + 1
           ,r.WorkWith
    FROM recCTE AS r
    WHERE r.nextPos<=LEN(r.WorkWith)
)
SELECT @periode AS ISO8601Periode
      ,MAX(Seconds) AS Seconds 
FROM recCTE;
GO

--You call it like this

DECLARE @SomePeriodes TABLE(p VARCHAR(100));
INSERT INTO @SomePeriodes VALUES('P3Y6M4DT12H30M5S'),('PT8H0M'),('PT7H30M'),('PT1H0M');

SELECT ISO2Sec.ISO8601Periode
      ,ISO2Sec.Seconds
      ,ISO2Sec.Seconds/(60*60) Hrs
FROM @SomePeriodes AS p
CROSS APPLY dbo.ConvertISO8601Periode2Seconds(p.p) AS ISO2Sec;
GO

--Clean up

DROP FUNCTION dbo.ConvertISO8601Periode2Seconds;

The result

ISO8601Periode      Seconds     Hrs
P3Y6M4DT12H30M5S    110550605   30708,5013888889
PT8H0M              28800       8
PT7H30M             27000       7,5
PT1H0M              3600        1

Some explanation

Regretfully ISO 8601 periods can use the M for months as well as for minutes. If there is a T within the string, the M after the T is the minute. I replace this with X in order to work straight through the string.

The central code is a recursive CTE trailing down the string char-by-char, remembering the position of the last number's start and looking for non-numbers. Whenever one letter is found, the numeric value before is multiplied accordingly and added to the previous value - thus accumulating all values.