Convert from nvarchar to datetime from a large record table with potentially bad date strings

Question

I have a main table called Cases that I am inserting data into. I have a alternative table where all of the raw data called rawTableData is stored and then sent to the main table.

I have a nvarchar column in my rawTableDatathat stores a datetime string in this format

2016-04-04-10.50.02.351232

I have a column in my Cases table that has a datatype of DATETIME.

I first tried to find the bad data in this method below

SELECT CONVERT(datetime, nvarcharDateColumn, 103)
FROM rawTableData
WHERE ISDATE(CONVERT(datetime, nvarcharDateColumn, 103)) != 1

And I get the error below

The conversion of nvarchar data type to a datetime data type resulted in an out-of-range value.

Then I tried a different approach hoping to find all of the out of range values

SELECT nvarcharDateColumn
FROM rawTableData
WHERE ISDATE(nvarcharDateColumn)

But that only returns all rows since its nvarchar.

Again, I kept going and tried a different approach

SELECT CONVERT(DATETIME, CASE WHEN ISDATE(nvarcharDateColumn) = 1 THEN nvarcharDateColumn END, 103)
FROM rawTableData

I am not sure what I am doing wrong here and any help would be appreciated.

I am using SQL Server 2012

Answer 1

You can use TRY_CONVERT:

SELECT nvarchardatecolumn, TRY_CONVERT(date, nvarchardatecolumn)
FROM rawTableData

And if you only want to return the invalid dates, use a derived table:

SELECT *
FROM (SELECT nvarchardatecolumn, TRY_CONVERT(date, nvarchardatecolumn) DateCheck
      FROM rawTableData
     ) A
WHERE DateCheck IS NULL