Detecting NULL dates and showing empty string in SSRS

19
votes

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.

2
sql-server-2005reportingservices-2005

2 Answers

27
votes

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.

-1
votes

use a code like this:

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