Convert dd/mm/yyyy to date in SQL Server

Question

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?

then you have bad data (something like `30/02/2015` or `12/13/2017`, etc) — Lamak, May 03 '17 at 16:43
The first and the third variant should work just fine. The second one (with style 110) is for another format: mm-dd-yyyy. — Razvan Socol, May 03 '17 at 16:45
Possible duplicate of [Sql Server string to date conversion](http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) — Tim Grant, May 03 '17 at 16:46
**John Cappelletti** you were right! I have many records where StartDate = 00/00/0000. I will use iif function to change it into null. Thank you so much! — inbal_bpr, May 03 '17 at 16:59
@inbal_bpr Try_Convert() will return a NULL value if the conversion fails — John Cappelletti, May 03 '17 at 17:01
John Cappelletti try_convert(date, StartDate, 103) returns '1900-01-01' where StartDate is null.. — inbal_bpr, May 03 '17 at 17:06
@inbal_bpr That is expected. I now understand your IIF() statement — John Cappelletti, May 03 '17 at 17:09

score 4 · Accepted Answer · answered May 03 '17 at 16:50

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

Convert dd/mm/yyyy to date in SQL Server

1 Answers1

Linked