Converting strings to DateTime in an IIF expression

Question

I am using SSRS 2008R2 to create reports. The datasource is xml response from webservice. I want to return empty string if there is no data, but if there is row with data I want to convert it to another datetime format.

I am using IIF construction like this:

=IIF
(
    LEN(Fields!DateOfReg.Value) <= 0,
    "",
    FORMAT(CDATE(DateTime.ParseExact(Fields!DateOfReg.Value,"M/d/yyyy hh:mm:ss tt",
    System.Globalization.CultureInfo.InvariantCulture)), "dd.MM.yyyy HH:mm:ss")
)

When there is no data it shows #Error in the textbox and in the output:

String was not recognized as a valid DateTime

Does it mean that the IIF construction calculates both TRUE and FALSE statements? If so how can I make it short-circuit and don't convert row if there is no data?

Answer 1

Try the following:

=IIF
(
    Fields!DateOfReg.Value= "",
    "",
    Datevalue(Cstr(Fields!DateOfReg.Value))
)

Answer 2

Yes, you are correct - IIF calculate both statements. But you can convert your empty string to "Nothing" using IIF and then try format it, something like this:

Format(IIF(Fields!DateOfReg.Value = "", Nothing, Fields!DateOfReg.Value), "yyyy/MM/dd")

Format function cause exception if you pass empty string but return empty string if you pass "Nothing".