How to convert MM/YYYY/DD format string to DATE in SQL Server?

I have a column for which I need to convert the data to DATE format. I receive below error whenever I encounter the data ‘MM/YYYY/DD’ format, but it works fine for other formats.

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

Sample data:

SELECT CAST('05/2020/29' AS DATE);

SELECT ISDATE('05/2020/29') returns 1 but CAST or CONVERT functions fail.

Answer

That’s a weird format. I would recommend datefromparts():

select datefromparts(
    substring(mycol, 4, 4), 
    left(mycol, 2), 
    right(mycol, 2)
) mydate
from mytable

Demo on DB Fiddle:

with mytable as (select '05/2020/29' mycol)
select datefromparts(substring(mycol, 4, 4), left(mycol, 2), right(mycol, 2)) mydate
from mytable

| mydate     |
| :--------- |
| 2020-05-29 |