How to convert the below text string into a date type using sql 2016 : ActivateDate	ShipDate	Month	Month_Length	Day-2c	Day-2c_Length	YEAR-201x	SHIPDateConcatenate	ActivateDateConcatenate
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
NULL	6/12/2018 12:00:00 AM	6	1	12	2	2018	6-12-2018	NULL
10/12/2018 14:45	10/16/2018 12:00:00 AM	10	2	16	2	2018	10-16-2018	10-12-2018

The two columns [ActivateDate] and [ShipDate] datatype is listed below; however each time i try to use convert() or cast() to a date type, a conversion error occurs.

Any assistance would be much appreciated.

Regards
TMilzSr

SELECT
[ActivateDate], -- '10/12/2018 14:45' nvarchar(100)
[ShipDate], -- '6/12/2018 12:00:00 AM'   nvarchar(100)

SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2) as 'Month',
LEN(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)) as 'Month_Length',

REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') as 'Day-2c',
LEN(REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','')) as 'Day-2c_Length',

SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'YEAR-201x',

SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])-2,2)
+'-'+REPLACE(SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/', iedimpr.[ShipDate])+1,2),'/','') 
+'-'+SUBSTRING(iedimpr.[ShipDate],CHARINDEX('/201', iedimpr.[ShipDate])+1,4) as 'SHIPDateConcatenate',

SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])-2,2)
+'-'+REPLACE(SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/', iedimpr.[ActivateDate])+1,2),'/','') 
+'-'+SUBSTRING(iedimpr.[ActivateDate],CHARINDEX('/201', iedimpr.[ActivateDate])+1,4) as 'ActivateDateConcatenate'