Reporting Services - handling an empty date?

Question

Hey, I have a report parameter which looks like this: 01.01.2009 00:00:00

Its a date (as string), as you might have guessed :). The problem is, this param can be an empty string as well. So I tried those expressions:

1. =IIf(IsDate(Parameters!DateTo.Value), CDate(Parameters!DateTo.Value), "")
2. =IIf(Len(Parameters!DateTo.Value) > 0, CDate(Parameters!DateTo.Value), "")

Both dont work and the value for the textfield where I print the expressions result is always #Error. As soon as I remove the CDate stuff, it works, but I have to use it. IS there another way to achieve that? What I want is to display nothing if its not a date or the date (format dd.mm.yyyy) if its a date.

Ideas?

Thanks :)

Answer 1

All arguments to the IIf are evaluated, which results in your error, since the CDate will fail for an empty string.

You can get around this by just writting a function along these lines, using a standard if statement:

Function FormatDate(ByVal s As String) As String
    If (s <> "") Then
        Return CDate(s).ToString()
    Else
        Return ""
    End If
End Function

Then call it with: =Code.FormatDate(Parameters!DateTo.Value)

Answer 2

First, fix your database to properly store dates rather than doing these workarounds. You probably have bad data in there as well (Feb 30 2010 for example or my favorite, ASAP). Truly there is no excuse for not fixing this at the database level where it needs to be fixed except if this is vendor provided software that you can't change (I would yell at them though, well notify them really, and ask them to fix their data model or go to a new product designed by someone who knows what they are doing. A vendor who can't use dates properly is likely to have software that is very poor all around). In the query that you use to select the infomation, have you considered just converting all non-dates to null?