Converting strings to DateTime in an IIF expression

a1an picture a1an · Feb 5, 2013 · Viewed 14.6k times · Source

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

Gil Peretz picture Gil Peretz · Feb 5, 2013

Try the following:

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