Detecting NULL dates and showing empty string in SSRS

Question

I'm trying to format some cells in a Reporting Services report that will contain DateTime? values - or not.

If the underlying data has a NULL for that DateTime?, I'd like to show nothing (empty cell) - and if that data source contains a value, I'd like to show the date in short date format (dd.MM.yyyy in my locale).

So I tried to put this formula into the relevant SSRS cells

=FormatDateTime(Fields!DatumBSE.Value, 2)

but now I'm getting 01.01.0001 for all NULL dates....

I can't seem to wrap my head around how to do this in a SSRS (VB) formula.... I tried using IsNothing() but that doesn't seems to really help - I can detect a NULL, but how to I tell the cell to show an empty string in that case?

Solution:

I ended up using this function:

=IIF(IsNothing(Fields!DatumBSE.Value), "", FormatDateTime(Fields!DatumBSE.Value, 2))

Seems to work just fine for me now.

Konstanz

Answer 1

I just tested the following expression and it replaced the null date with an empty string:

=IIF(Fields!DatumBSE.Value is nothing, nothing, FormatDateTime(Fields!DatumBSE.Value, 2))

The other suggestion that I would make is that you could format the date to the correct format in the report dataset by placing a CASE expression around the date value.

Perpetuate

That's pretty close to the solution I decided to go with - thanks! I didn't want to create a view that sometimes returns a DateTime and other times returns an empty string - I'd much rather handle that in the report itself. – Konstanz

Answer 2

use a code like this:

If(isNull([date field]),Null, elsequote)

Anthologize

This would go in the SQL that creates the database. But the OP problem is an issue in the reporting layer (SSRS) and is better handled in the reporting layer. Also I believe there are several other problems with this answer. – Stutsman