Convert dd/mm/yyyy to date in SQL Server

tsql date varchar sql-convert
inbal_bpr picture inbal_bpr · May 3, 2017 · Viewed 11.3k times · Source

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?

Answer

John Cappelletti picture John Cappelletti · May 3, 2017

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

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
Count work days between two dates

How can I calculate the number of work days between two dates in SQL Server? Monday to Friday and it must be T-SQL.

Read More