2

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.

3

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.

  • Thanks. This worked perfectly. – Keets Aug 12 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.