To convert a string to date uses sql conversion functions like cast, convert, try_cast, try_parse, try_convert.

Syntax

CAST ( expression AS datatype [ ( length ) ] )

CONVERT ( datatype [ ( length ) ] , expression [ , style ] )

TRY_CAST ( expression AS datatype [ ( length ) ] )

TRY_PARSE ( string_value AS datatype [ USING culture ] )

TRY_CONVERT ( datatype [ ( length ) ], expression [, style ] )

Examples

SELECT Cast('05/17/2017' as DATE);
SELECT Cast('05/17/2017' as DATETIME);
SELECT Cast('05/17/2017' as DATETIME2);
SELECT Cast('05/17/2017' as SMALLDATETIME);
SELECT Cast('05/17/2017' as DATETIMEOFFSET);

SELECT TRY_CAST('05/17/2017' as DATE);

SELECT Convert(VARCHAR(15),'05/17/2017',101);
SELECT Convert(VARCHAR(15),'05.17.2017',101);

SELECT TRY_PARSE('05/17/2017 19:15:33 PM' AS DATE USING 'en-us');
SELECT TRY_PARSE('05/17/2017 19:15:33 PM' AS DATETIME USING 'en-us');

SELECT TRY_CONVERT(VARCHAR(15),'05/17/2017',101);