Detecting NULL dates and showing empty string in SSRS

sql-server-2005 reportingservices-2005
marc_s picture marc_s · Jan 30, 2013 · Viewed 55.7k times · Source

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.

Answer

Taryn picture Taryn · Jan 30, 2013

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.

Related questions

SQL 2005 Reporting Services if check for null

In SSRS 2005 I have a table with a dataset linked to it. I want to check if the value of a field is null and if it is not null then format the data to make sure it has one …

Read More
Variables to input in file name for Reporting Services subscriptions

Is there a list anywhere that lists all the variables I can use in file names for report subscriptions from within SQL Server Management Studio? I currently use @timestamp in my file names, but would like to use other options …

Read More
Microsoft.ReportingServices.Interfaces.dll missing for SSRS 2005

I am missing this DLL reference in c:\program files\SQL Server\90\Tools\Binn. I'm assuming that this is because I didn't install the 2005 client. I installed all of the services for 2005, the database instance, reporting services instance, SSAS and …

Read More