How to validate dates are in the format of YYYYMMDD

I have a date column having thousands of rows in the format of YYYYMMDD. I have to validate that all the rows are in the correct date format. If there is any row off from the desired format or in any means (say null) the row should be returned in the result. I have the following sample data:

  Date           value
 20180101         1
 20180102         2
 20180203         3
 Nan              4
 20180809         5
 08092029         7
 2018-05-06       8

Since NAN and 2018-05-06 are not in the desired date format, I want return of these rows. I am aware that ISDATE() is available in SQL server, but it gives true value when the date is in any format.

Answer

This is a little tricky. But I think this does what you want:

(case when date like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and
           try_cast(date, date) is not null
      then 'Valid'
      else 'Yuck!'
 end)

SQL Server treats a string of 8 digits as a date in the format as YYYYMMDD as a date, regardless of other settings. So the above should do exactly what you want.