SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select  @@VERSION;


(No column name)


Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
 Dec 12 2019 15:14:11 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE ticket
(
  ticket_id INTEGER NOT NULL,
  working_time VARCHAR (30) NULL DEFAULT NULL CHECK (working_time != '')
);
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');

INSERT INTO ticket VALUES (20, '20.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, '_rubbish__');
INSERT INTO ticket VALUES (20, '20.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, NULL);

INSERT INTO ticket VALUES (20, '21.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, '_rubbish__');
INSERT INTO ticket VALUES (20, '21.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, NULL);
11 rows affected

SELECT * FROM ticket;


ticket_id
working_time


18
20.02.2021,15:00,17:00

18
20.02.2021,15:00,17:00

18
20.02.2021,15:00,17:00

20
20.02.2021,12:00,14:15

20
_rubbish__

20
20.02.2021,12:00,14:15

20
null

20
21.02.2021,12:00,14:15

20
_rubbish__

20
21.02.2021,12:00,14:15

20
null


WITH cte AS
(
  SELECT
    ticket_id,
    CAST
    (
      TRY_CONVERT
      (
        DATE, 
        SUBSTRING(working_time, 7, 4) + '.' + 
        SUBSTRING(working_time, 4, 2) + '.' +
        SUBSTRING(working_time, 1, 2)
      ) AS DATETIME
    ) 
    + 
    CAST
    (
      CAST
      (
        SUBSTRING
        (
          working_time, 12,  5
        ) AS TIME
      ) AS DATETIME
    ) AS st_dt,
    CAST
    (
      TRY_CONVERT
      (
        DATE, 
        SUBSTRING(working_time, 7, 4) + '.' + 
        SUBSTRING(working_time, 4, 2) + '.' +
        SUBSTRING(working_time, 1, 2)
      ) AS DATETIME
    ) 
    + 
    CAST
    (
      CAST
      (
        SUBSTRING
        (
          working_time, 18,  5
        ) AS TIME
      ) AS DATETIME
    ) AS et_dt
  FROM
    ticket
)
SELECT
  ticket_id AS "Ticket ID",
  TRY_CONVERT(date, et_dt) AS "The date",
  TRY_CONVERT
  (
    VARCHAR(8), 
    dateadd
    (
      second, 
      COALESCE(SUM
      (
        DATEDIFF(SECOND, st_dt, et_dt)
      ), 0), 
      0
    ),  
    108
  ) AS hrs_worked_per_ticket
FROM 
  cte
WHERE TRY_CONVERT(DATE, et_dt) IS NOT NULL
GROUP BY ticket_id, TRY_CONVERT(DATE, et_dt)
ORDER BY ticket_id, TRY_CONVERT(DATE, et_dt);


Ticket ID
The date
hrs_worked_per_ticket


18
2021-02-20
06:00:00

20
2021-02-20
04:30:00

20
2021-02-21
04:30:00

ticket_id	working_time
18	20.02.2021,15:00,17:00
18	20.02.2021,15:00,17:00
18	20.02.2021,15:00,17:00
20	20.02.2021,12:00,14:15
20	_rubbish__
20	20.02.2021,12:00,14:15
20	null
20	21.02.2021,12:00,14:15
20	_rubbish__
20	21.02.2021,12:00,14:15
20	null

Ticket ID	The date	hrs_worked_per_ticket
18	2021-02-20	06:00:00
20	2021-02-20	04:30:00
20	2021-02-21	04:30:00