Search code examples
reporting-servicesreportbuilder3.0ssrs-expression

DATEDIFF returns Negative Values -SSRS


This is my expression.

=DateDiff(DateInterval.Day,Fields!scheduledstartValue.Value,Fields!actualendValue.Value )

It returns both positive and negative numbers. Sometimes when a user does not enter an 'actualendValue', it returns a negative number like '-1234'.

What i'm trying to do with this equation (below) is , if a negative value is returned, display a '0'.

IIf([Fields!scheduledstartValue.Value]="","",IIf(Nz(DateDiff('d',[Fields!scheduledstartValue.Value],[Fields!actualendValue.Value]),0)>0,Nz(DateDiff('d',[Fields!scheduledstartValue.Value],[Fields!actualendValue.Value]),0),0))

Can anyone help me , with my second equation or is there a better way to do this?,I'm just about to pull my hair out.

Many Thanks.

UPDATE 1: I have tried :

=IIF(DateDiff(DateInterval.Day,Fields!scheduledstartValue.Value,Fields!actualendValue.Value ) = 0,"NA",DateDiff(DateInterval.Day,Fields!scheduledstartValue.Value,Fields!actualendValue.Value ))

When I run this expression I get not errors, and I still get positive and negative values.


Solution

  • SOLUTION:

     =IIF(Fields!actualendValue.Value is nothing, nothing, DateDiff(DateInterval.Day,Fields!scheduledstartValue.Value,Fields!actualendValue.Value ))