This is a pain in Oracle as it gives a database error - I've used Tableau Calculations to do the same thing.
I've attached an example with two dates - one valid and one not (one is 29 Feb 1982 - which was not a leap year and therefore not a real date).
I did this in two Tableau Calc's - the first cut it down to 9 characters, the second grabbed the DD MM and YY fields and then combined them and converted them into a date. When presented with an invalid date Tableau doesn't throw an error like Oracle - it just returns a Null.
You could of course have done it all on one calc (using MID() instead of Right() for the days) but I just built it up this way.
To get the word Unknown for a NULL record you can just use an If IsNull() to test if it is null
Packaged workbook attached.
Hope this helps.
Date Calcs.twbx.zip 7.6 KB
I tried my own interpretation of your logic without success and then copied yours exactly, but still get the same error. I'll spend some more time trying to figure it out. Any other ideas are much appreciated!
If you can send me a packaged workbook - I'll give it a go for you.