Get 2 dates from within brackets-CodePudding

I have a requirement in SQL Server to get the 2 dates inside the brackets

Example: Billing for(1 Aug 2020 - 30 Oct 2020)

Expected to have this in two columns:

Fromdate         Todate
01/08/2020       30/10/2020

Select substring to get the value but not sure how to do both in one query.

CodePudding user response：

Use Convert and Concat:

Select *, 
Concat(
    'Billing for(', 
    Convert(VarChar(50), Fromdate, 106), 
    ' - ', 
    Convert(VarChar(50), Todate, 106),
    ')') As Period 

From YourTable

The reverse operation:

Select 
    Convert(DateTime, SubString([Field Name], PatIndex('%(%', [Field Name])   1, 11)) As Fromdate,
    Convert(DateTime, SubString([Field Name], PatIndex('%)%', [Field Name]) - 11, 11)) As Todate

CodePudding user response：

Please try the following solution based on tokenization via XML and XQuery.

It will work starting from SQL Server 2017 onwards due to dependency on the CONCAT_WS() function. If you are on some earlier version of SQL Server, it could be replaced with the CONCAT() function.

As an intermediate result, the t(c) contains the following XML:

<root>
  <r>1</r>
  <r>Aug</r>
  <r>2020</r>
  <r>-</r>
  <r>30</r>
  <r>Oct</r>
  <r>2020</r>
</root>

After that it is simple XQuery extracting date parts based on position and compose real dates.

SQL

DECLARE @input VARCHAR(100) = 'Billing for(1 Aug 2020 - 30 Oct 2020)'
    , @separator CHAR(1) = SPACE(1);

;WITH rs AS
(
    SELECT @input AS input
        , CHARINDEX('(', @input) AS [start]
        , CHARINDEX(')', @input) AS [end]
)
SELECT rs.input, c
    , TRY_CAST(CONCAT_WS('-'
        , c.value('(/root/r[3]/text())[1]', 'CHAR(4)')
        , c.value('(/root/r[2]/text())[1]', 'CHAR(3)')
        , c.value('(/root/r[1]/text())[1]', 'CHAR(2)')) AS DATE) AS FromDate
    , TRY_CAST(CONCAT_WS('-'
        , c.value('(/root/r[7]/text())[1]', 'CHAR(4)')
        , c.value('(/root/r[6]/text())[1]', 'CHAR(3)')
        , c.value('(/root/r[5]/text())[1]', 'CHAR(2)')) AS DATE) AS ToDate
FROM rs
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(SUBSTRING(input, [start] 1, [end] - [start]-1), @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t(c);

Output

input	FromDate	ToDate
Billing for(1 Aug 2020 - 30 Oct 2020)	2020-08-01	2020-10-30

CodePudding user response：

with dates as (
     select 
        cast('2020/08/01' as date) date1, 
        cast('2020/10/30' as date) date2
  ) 
select 
   concat('Billing for (',format(date1,'dd MMM yyyy'),' - ',format(date2,'dd MMM yyyy'),')') 
from dates;

output: Billing for (01 Aug 2020 - 30 Oct 2020)

see: DBFIDDLE;