Search code examples
datetimereporting-servicesssrs-2008

Format Hours/Minutes in SSRS Expression

I was trying to combine two separate date/time fields into one text box through an expression in SSRS.

My expression is:

=Format(Fields!EarlyShiftStart.Value,"hh:mm tt") & "-" & Format(Fields!LateShiftEnd.Value,"hh:mm tt")

And it looks to be correct in the report:

Drive_Times

But I am getting a warning when I preview the report:

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox49.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format

Not sure why this warning occurs, as it looks to be correct. Thoughts?

Solution

  • I am not sure about it but there is seems to be the wrong input string getting from the database. My best guess is that there are 2 reasons in your case this was happening
    1) Some where in your data might present the null value. If you want to avoid the warning I will suggest to you that use the expression as,

     =Format(IIF(IsNothing(Fields!EarlyShiftStart.Value),"00:00",Fields!EarlyShiftStart.Value),"hh:mm tt") & "-" & Format(IIF(IsNothing(Fields!LateShiftEnd.Value),"00:00",Fields!LateShiftEnd.Value),"hh:mm tt")

    This will filter out the incoming NULL values.

    2) You are concatenating the two values with - so that could be the reason as you are formatting both your values as Time and then concatenating with string value. So try to change your expression as,

     =CStr(Format(Fields!EarlyShiftStart.Value,"hh:mm tt")) & "-" & CStr(Format(Fields!LateShiftEnd.Value,"hh:mm tt"))

    This will force convert your time values to the string and then con-cat them.

    I would also suggest you to look at your incoming values if all values are in the correct format. So you can make changes in your expression accordingly.