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.
Because of data type precedence, the compiler is trying to convert
# to a date.
So you need to convert the
date value back to a
varchar instead, in the style you want. A shorter syntax is to use
SELECT ISNULL( CONVERT(varchar(20), TRY_CONVERT(date, table.CalendarColumn, 104), 23), table.CalendarColumn) AS Calendar FROM....