How to use string function within CAST/CONVERT in t-sql (Azure Synapse)-CodePudding

I have a table in Azure Synapse as below,

want to query the Enrolled_period column into 2 different column as DATE datatype.

I am trying following query

  select convert(varchar, SUBSTRING(enrolled_period, 2, 12), 23) as startdate
from dbo.test_period;

also trying to split the data

select 
REPLACE(PARSENAME(REPLACE(enrolled_period, ', ', '.'), 2), '(', '') AS startdate, 
REPLACE(PARSENAME(REPLACE(enrolled_period, ', ', '.'), 1), ')', '') AS enddate 
from dbo.test_period

But getting error

Conversion failed when converting date and/or time from character string.

How can I solve this?

CodePudding user response：

I created a test table and tried this, its working:

Please check :

select 
cast ( SUBSTRING(p,3,10) as date) , cast ( SUBSTRING(p,16,10) as date),p
from test2

CodePudding user response：

Before trying to cast/convert to a date it's prudent to test what the resulting substring looks like.

For example :

declare @enrolled_period varchar(30);
set @enrolled_period = '(''2021-10-11'', ''2021-10-31'')';

select @enrolled_period as enrolled_period
, substring(@enrolled_period, 3, 10) as start1
, substring(@enrolled_period, 17, 10) as end1
, RIGHT(PARSENAME(REPLACE(@enrolled_period, ''', ''', '.'), 2), 10) as start2
, LEFT(PARSENAME(REPLACE(@enrolled_period,  ''', ''', '.'), 1), 10) as end2

select @enrolled_period as enrolled_period
, CAST(SUBSTRING(@enrolled_period, 3, 10) AS DATE) as startdate
, CAST(SUBSTRING(@enrolled_period, 17, 10) AS DATE) as enddate

The 3th param for substring is a length, not a position.

CodePudding user response：

you need to remove "-"

select D.[start_date]
    ,D.[end_date]
from dbo.test_period T
    outer apply
    (
        select 
            replace(replace(replace(replace(replace(replace(
                T.enrolled_period
                ,'(', '')
                ,')', '')
                ,'''', '')
                ,',', '')
                ,'-', '')
                ,' ', '') as clean_period
    ) C
    outer apply
    (
        select
            cast(left(C.clean_period, 8) as date) as [start_date]
            ,cast(right(C.clean_period, 8) as date) as [end_date]
    ) D