How can I display NULL values in formatted datetimeoffset column?

1
votes

SSRS - Report Builder 3.0

We have recently migrated all our SQL Server datetime columns to datetimeoffset columns. This has had a deleterious effect on one of our reports.

The report references a NULLABLE datetimeoffset column called TerminalStateDateTime. I wish to use the following formatting:

= Format(Fields!TerminalStateDateTime.Value.DateTime, "dd MMM yyyy hh:mm")

Unfortunately, when the value of TerminalStateDateTime is NULL, it shows #Error. I've tried using IIF and Switch to trap this, but nothing works:

=IIF(IsNothing(Fields!TerminalStateDateTime.Value), "", Format(Fields!TerminalStateDateTime.Value.DateTime, "dd MMM yyyy hh:mm"))

enter image description here

Does anyone have any idea how to disregard NULL values and yet correctly format non-NULL values?

Thanks

Edward

1
sql-serverreporting-servicesssrs-2008

1 Answers

1
votes

Your easiest option is to simply apply the formatting at the Textbox, not via an expression.

With some simple data:

select TerminalStateDateTime = cast('20140101' as datetimeoffset)
union all select TerminalStateDateTime = cast('20140201' as datetimeoffset)
union all select null

enter image description here

I have a simple table:

enter image description here

The first column simply displays the value. The second uses your expression, i.e.

= Format(Fields!TerminalStateDateTime.Value.DateTime, "dd MMM yyyy hh:mm")

The third applies the same format, but in the textbox properties:

enter image description here

You can see this doesn't give #Error:

enter image description here