Split column, and then convert different date types to standard date format (YYYY-MM-DD) - SQL Server

Question

I have requirement to split column in 2 separate columns and then convert multiple date types column to standard date format column.

CREATE TABLE Report
(Id INT, Reference VARCHAR(30));

INSERT INTO Report
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')

SELECT * FROM Report

Id	Reference
1	Location (11/8/22)
2	Timesheet (11/10/22)
3	TESTING (12/09/22)
4	Incorrect Payment (9/10/22)
5	Employee (11/9/22)
6	Authorization
7	Inactive Client
8	Active Client (07/22/2022)
9	TESTING (09/09/22)
10	Timesheet

The output I need:

Id	Reference	Type	Date
1	Location (11/8/22)	Location	2022-11-08
2	Timesheet (11/10/22)	Timesheet	2022-11-10
3	TESTING (12/09/22)	TESTING	2022-12-09
4	Incorrect Payment (9/10/22)	Incorrect Payment	2022-09-10
5	Employee (11/9/22)	Employee	2022-11-09
6	Authorization	Authorization	NULL
7	Inactive Client	Inactive Client	NULL
8	Active Client (07/22/2022)	Active Client	2022-07-22
9	TESTING (09/09/22)	TESTING	2022-09-09
10	Timesheet	Timesheet	NULL

I was able to split the Reference column, however can't find the way to remove "(" and ")" and convert it to Standard Date format. Is it even possible to convert the way I need, because date information is from manual user input, therefore 2022-09-08 can be present in a multiple ways, such as: 09/08/2022, 09/08/22, 09/8/22, 9/8/2022 and any other possible ways.

SELECT 

   p.*

   ,SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
        END) AS Type

   ,SUBSTRING(p.Reference, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference) + 1
        ELSE CHARINDEX('(', p.Reference) + 0
        END, 1000) AS Date

FROM Report AS p

Id	Reference	Type	Date
1	Location (11/8/22)	Location	(11/8/22)
2	Timesheet (11/10/22)	Timesheet	(11/10/22)
3	TESTING (12/09/22)	TESTING	(12/09/22)
4	Incorrect Payment (9/10/22)	Incorrect Payment	(9/10/22)
5	Employee (11/9/22)	Employee	(11/9/22)
6	Authorization	Authorization
7	Inactive Client	Inactive Client
8	Active Client (07/22/2022)	Active Client	(07/22/2022)
9	TESTING (09/09/22)	TESTING	(09/09/22)
10	Timesheet	Timesheet

score 3 · Accepted Answer · answered Dec 15 '22 at 07:07

SELECT Id, Reference, 
       MAX(CAST(CASE CHARINDEX(')', value) 
                   WHEN 0 THEN  NULL
                   ELSE REPLACE(value, ')', '')
                END AS DATE)) AS TheDate
FROM   Report
       OUTER APPLY STRING_SPLIT(Reference, '(')
GROUP  BY Id, Reference;

As you can see your table violate the first normal form (1FN) by having non atomic values into a column... Then this is not a relational database but something close to CoBOL files in the old times...

Then querying is difficult and performances will be poor. You better have to normalize your DB !

RF1991 · Answer 2 · 2022-12-15T07:18:23.463

your data

declare @a table
(Id INT, Reference VARCHAR(100));

INSERT INTO @a
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')

you should use Substring and Charindex to identify () and LIKE and IIF operator to have null value for date if value don't have ()

SELECT id,
       reference,
       IIF(reference LIKE '%(%', Substring(reference, 0,
                                 Charindex('(', reference)),
       reference)  as Type,                                                     
       IIF(reference LIKE '%(%', Substring(reference, Charindex('(', reference)
                                                      + 1,
                                 Charindex(')', reference)
                                 - Charindex('(', reference)
                                 - 1), NULL) AS Date
FROM   @a

dbfiddle

Hardik Patel · Answer 3 · 2022-12-15T07:16:18.460

1

;with cte as(
SELECT 
   p.id,
   p.reference

   ,SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
        END) AS Type

   ,REPLACE(SUBSTRING(p.Reference, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference) + 1
        ELSE CHARINDEX('(', p.Reference) + 1
        END, 1000),')','') AS Date1

FROM #Report AS p)
select *, CASE WHEN date1 IS NULL OR date1 = '' THEN NULL ELSE CAST(date1 AS DATE) END from cte

edited Dec 15 '22 at 07:16

answered Dec 15 '22 at 07:09

Hardik Patel

113
7

1

You should always have an explanation in addition to a working query. – Dale K Dec 15 '22 at 07:52

ahmed · Answer 4 · 2022-12-16T22:00:37.563

You could try using STRING_SPLIT function as the following

WITH split_ref As(
  SELECT R.Id, R.Reference, SP.value, SP.ordinal
  FROM Report R
  CROSS APPLY STRING_SPLIT(Reference, '(', 1) AS SP
)
SELECT Id, Reference,
       MAX(CASE ordinal WHEN 1 THEN value END) AS Type,
       MAX(CASE ordinal WHEN 2 THEN TRY_CAST(REPLACE(value, ')', '') AS DATE) END) AS [Date] 
FROM split_ref
GROUP BY Id, Reference
ORDER BY Id

For SQL Servers versions that doesn't support ordinal, try the following:

WITH split_ref As(
  SELECT R.Id, R.Reference, SP.value, 
    CASE WHEN SP.value LIKE '%)' THEN 2 ELSE 1 END AS ordinal
  FROM Report R
  CROSS APPLY STRING_SPLIT(Reference, '(') AS SP
)
SELECT Id, Reference,
       MAX(CASE ordinal WHEN 1 THEN value END) AS Type,
       MAX(CASE ordinal WHEN 2 THEN TRY_CAST(REPLACE(value, ')', '') AS DATE) END) AS [Date] 
FROM split_ref
GROUP BY Id, Reference
ORDER BY Id

See demo.

Split column, and then convert different date types to standard date format (YYYY-MM-DD) - SQL Server

4 Answers4