Convert from varchar into date in SQL Server

Question

This looks easy solution but I can't seem to figure out as to why this is not working for me. I have a column that has data like this:

DateField
----------
12/16/2016
11/06/2016

All I want to do is to convert from varchar into a date column, but I am getting this error:

Conversion failed when converting date and/or time from character string.

Here is my simple query:

select convert (date, DateField) as convertedField 
from myTable

Answer 1

Nothing wrong with the two examples you have given. There are some bad dates in your table which cannot be converted to date.

Use TRY_CONVERT function for bad dates it will return NULL

select TRY_Convert(date,DateField)
From myTable

You should always store dates in DATE/DATETIME datatype.

If you want to see the records which cannot be converted to date then

select DateField
From myTable
Where TRY_Convert(date,DateField) IS NULL