Convert Crystal Reports string to Date format yyyy-MM-dd

I have a Crystal Reports formula field that returns a string in the format dd.MM.yy. How do I create a CR formula to convert this to a Date in the format yyyy-MM-dd?

All dates are form the year 2000 and later. I have tried this so far:

Date(2000 + right({@formula}, 2), mid({@formula}, 4, 2), left({@formula}, 2))

Solution

I would first convert the text to date and then back to string. That way it is easiest to comprehend the workings of different casts.

ToText(DateValue(
    2000 + ToNumber(Split({@formula}, '.')[3]) , ToNumber(Split({@formula}, '.')[2]), ToNumber(Split({@formula}, '.')[1])
), "yyyy-MM-dd")

If you get errors with this conversion you might want to make sure the input string is in correct format:

If NumericText(Split({@formula}, '.')[1]) and NumericText(Split({@formula}, '.')[2]) and NumericText(Split({@formula}, '.')[3])  Then
    ToText(DateValue(
        2000 + ToNumber(Split({@formula}, '.')[3]) , ToNumber(Split({@formula}, '.')[2]), ToNumber(Split({@formula}, '.')[1])
    ), "yyyy-MM-dd")
Else "Incorrect string"

(Tested with CR 2008)