'Conversion Failed in a CASE expression

I have a column that is of varchar type, it contains dates and '#':

31.01.2022
31.01.2022
30.11.2021
31.12.2021
30.11.2021
28.02.2022
28.02.2022
#

I am trying to convert the dates to the standard date format (YYYY-MM-DD) and leave the '#' as it is whenever it occurs.

Here is my code:

CASE 
    WHEN table.CalendarColumn='#' THEN '#'
    ELSE CONVERT(date, table.CalendarColumn, 104) END AS Calendar

The outcome column is also of varchar(10) type (same as the original column).

I expected to get # whenever the cell is # and the converted Date whenever it is not # but I get this error instead:

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



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source