How to combine date and time with a string value in column created by Case in SQL Query

Question

I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression.

I want to create this column to just display the date and time value if its not null but if its NULL I want it show something like 'No Data'.

The Expression I have is:

Case 
  When (Column with Date/Time) is not null then (Column with Date/Time)
  When (Column With Date/Time) is NULL then 'No Data'
End AS Column A

However, this query returns an error:

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

I guess because it can't have a string value with a column formatted for Date and time?

Is there any way I can combine these two? If not I know I can leave it as is and just show the NULL value, but I wanted it to look cleaner.

Answer 1

I guess because it cant have a string value with a column formatted for Date and time?

You are correct. CASE Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Data type precedence comes into play here.

You'd have to convert your column to a varchar.

DB FIDDLE

case
   when DateTimeColumn  is null then 'No Data'
   else convert(varchar (20),DateTimeColumn,120)
end

You can reference the CAST and CONVERT documentation for other date/time formats.