Search code examples
t-sqldatevarcharsql-convert

Convert dd/mm/yyyy to date in SQL Server

I'm going nuts trying to convert a string type column into date.

The column name is StartDate, which contains a string date format dd/mm/yyyy. The field type is varchar(3000).

I tried the following:

CONVERT(datetime, StartDate, 103)

CAST(CONVERT(VARCHAR(10), StartDate, 110) AS DATE)

CONVERT(DATE, RIGHT(StartDate, 4) + '-' + SUBSTRING(StartDate, 4, 2) + '-' + LEFT(StartDate, 2), 126)

and other similar combinations.

I keep getting "out of range" and "conversion failed" error messages.

Does anyone have a creative solution?

Solution

  • I suspect you have some bogus data. For example

     Select try_convert(date, '15/07/2014', 103)

    Returns

    2014-07-15

    If 2012+, I would suggest that you 

    Select *
 From YourTable
 Where try_convert(date, StartDate, 103) is null

    This will identify your problem areas