SSRS: Simple Code Examples of Handling Null or Blank Date Fields

If you are looking for either null or blank values for a date field in SQL Server Reporting Services (SSRS) you will find that checking for IsNothing only identifies the Null values, but ignores the blank values. So what’s the quickest and easiest way to do this?

The simplest way to get both Null and Blank is to cast the date to a string using CSTR and then to do an IIF on the result.

Below is an example of getting a Null or a blank date value for a date from the previous row and outputting either the date value if one exists, or showing a default date such as January 1st 1960 instead of Null or blank values. This would probably be most useful for handling comparisons

=IIF(CSTR(previous(Fields!TestDate.Value)) = "", CDATE("1960-01-01"), previous(Fields!TestDate.Value) )

Here is the same statement with a null check only:

=IIF(IsNothing(previous(Fields!TestDate.Value)),CDATE("1960-01-01"), previous(Fields!TestDate.Value))

Let’s say that you want to format the output of the date field and want to show an empty string rather than a default date. To do this we could modify the first example statement above to return a Nothing value to SSRS. Note that here we are showing the results of the current row rather than of the previous row.

=IIF(CSTR(Fields!TestDate.Value) = "", Nothing, Fields!TestDate.Value )