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.

CodePudding user response：

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 TRY_CONVERT and ISNULL

SELECT
  ISNULL(
    CONVERT(varchar(20),
      TRY_CONVERT(date, table.CalendarColumn, 104),
      23),
    table.CalendarColumn) AS Calendar
FROM....

db<>fiddle