SSRS Report Shows #Error on DateTime Field

Question

I have a query that shows DateTime field with the name of “PostedOn” in SSRS. The Problem is that when I view my report using ASP.Net, report shows #Error only on PostedOn Field. But when I view my report in Designer View than it shows the PostedOn Date correctly. If I use

= Fields!PostedOn.Value

the direct value, it shows the PostedOn Column empty on SSRS. And if I use

= CDate(Fields!PostedOn.Value).ToShortDateString() 

than it particularly bounces back with the #Error. Designer shows the correct Date e.g. 08/06/2012.

When I view this report from ASP.Net Application. It Shows #Error. I have checked it with

=Iif(IsNothing(Fields!PostedOn.Value),"",
CDate(Fields!PostedOn.Value).ToShortDateString())' 

as well but no luck so far. #Error is shown on the Report. Please help me on this. Thanks in advance.

Answer 1

Maybe value for PostedOn is NULL when you call report from app, and when you try do some functions in expression with null value, often gives you error.

When SSRS evaluate expression with iif clause, then all parts are evaluated respectively: condition, true value, and false value, and at the end is done decision what to shov.

You have function ToShortDateString() on PostedOn field, in Iif expression, so maybe that giving you an error when PostedOn is NULL.

Try examine what app send to report.

Answer 2

You should be checking with the datetime min value

 =IIf(CDate(Fields!PostedOn.Value)=CDate("1/1/0001"),Nothing,
 CDate(Fields!PostedOn.Value).ToShortDateString()) 

Check this article