Converting Varchar(200) to YYYY-MM-DD format in Teradata SQL

I have a Varchar like so:

23FEB2025

I am trying to convert it into a format like:

1994-02-23 or YYYY-MM-DD

I have tried select cast ('23FEB2025' as date format 'yyyy-mm-dd'); and sel convert(date,'23FEB2025')

There are other dates in the column that are formatted like 12DEC65. I am now starting to assume that there is no simple way to convert this so I am asking for a little guidance. Would i need to take sub strings of the date and use a bunch of select case statements?

I was hoping to find a short way to do this but it seems there might not be one. I read on here that storing dates as a string is a bad idea and I fully subscribe to that notion now. Thank you for any help or advice!

Answer

The format portion of casting a date is the input format. The output format is based on your locale and date settings. In your case, you want this:

select
cast ('23FEB2025' as date format 'ddMMMYYYY')

Which will return 2025-02-23.