CASE WHEN Datatype Mismatch

Question

When the date is NULL I want to display 'N/A', but if the date is not null I want to display the date in the dB. This is what I currently have and I get a DataType Mismatch in the THEN/ELSE expressions. I am assuming it is because I am trying to display a character in a date field.

SELECT
CASE WHEN created_at IS NULL 
     THEN 'N/A' 
     ELSE created_at 
END AS created_at
FROM example.example_id

Is this possible?

Answer 1

Your assumption is correct: you can't mix a date(time) datatype with a varchar datatype. The solution is to cast the date to a varchar:

SELECT CASE WHEN created_at IS NULL THEN 'N/A' 
            ELSE CAST (created_at as char(8) format 'YYYYMMDD') 
       END AS created_at 
FROM example.example_id

the convert here may need some tweaking depending how you want it displayed.

Answer 2

Simply you can try

SELECT CAST(VARCHAR, ISNULL(Created_at, 'N/A'))  As 'Created_at' 
FROM example.example_id

Answer 3

You can't mix data types.

SELECT COALESCE(CAST(created_at AS VARCHAR(100)),'NA') AS created_at
  FROM example.example_id

Alternatively, if you are just concerned about presentation, you can just adjust your user preferences. ex. SQL Assistant -> Options -> Data Format -> then set 'Display this string for Null data values' as 'NA'.

Answer 4

Instead of CASE you can use COALESCE, too, but you still need to convert the date to a string first:

Coalesce(To_Char(created_at, 'yyyy-mm-dd'), 'N/A')