SSRS Datediff Null dates

mgmar · February 26, 2016, 3:19pm

Hi Everyone,

I am trying to evaluate two report items but I am getting an error because sometimes ReportItems!MSS_DATE_TIME.value is null so I am getting an error
My ISNOTHING statement isn't doing the trick

Does anyone have any suggestions?


=IIf(ISNOTHING(Fields!MSS_DATE.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS","N/A"))))

Thanks,

M

Muj9 · February 26, 2016, 3:55pm

try this

=IIf(ISNOTHING(Fields!MSS_DATE.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,isnothing(ReportItems!MSS_DATE_TIME.Value),0),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,isnothing(ReportItems!MSS_DATE_TIME.Value),0),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,isnothing(ReportItems!MSS_DATE_TIME.Value),0),"MSS","N/A"))))

mgmar · February 26, 2016, 4:06pm

Thanks for the reply Muj

I am getting all errors with this when in my previous SSRS statement I was only getting #Error on the nulls

Muj9 · February 26, 2016, 4:08pm

You said your ReportItems!MSS_DATE_TIME.Value filed isnull sometimes so isnothing function will change a null to 0

Muj9 · February 26, 2016, 4:10pm

Well try this

=IIf(ISNOTHING(Fields!MSS_DATE.Value),0,
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS","N/A"))))

Muj9 · February 26, 2016, 4:10pm

you can always use your old method if this fails

mgmar · February 26, 2016, 4:25pm

Yes I see what your saying I am going to try to fix this on the SSMS side and change values there to 0 instead of null

Thanks alot Muj

Muj9 · February 26, 2016, 4:35pm

Yes in your SSMS wrap your filed around isnull(YOURFIELD,0) that should do the trick

mgmar · February 26, 2016, 4:40pm

It changed it to 0 it worked
but my statement needs adjusting if 0 then RN


=IIf(ISNOTHING(Fields!MSS_DATE.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS","N/A") )))

throws errors

this statement
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS"

0 will always be smaller but I want to mark it as RN if MSS_DATE_TIME = 0

Muj9 · February 26, 2016, 4:44pm

=IIf(Fields!MSS_DATE.Value = 0,"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS","N/A") )))

mgmar · February 26, 2016, 4:45pm

yeah I tried that throws errors
ughh

Muj9 · February 26, 2016, 4:47pm

what is the error?

mgmar · February 26, 2016, 4:48pm

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox34.Paragraphs[0].TextRuns[0]’ contains an error: Operator '=' is not defined for type 'Date' and type 'Integer'.

Muj9 · February 26, 2016, 4:51pm

so if you was to just run =Fields!MSS_DATE.Value whould you get 0? or is it a dateformat

Muj9 · February 26, 2016, 4:52pm

you need to know what is producing 0 ?

Muj9 · February 26, 2016, 4:55pm

try this

=IIf((ISNOTHING(Fields!MSS_DATE.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = 0 OR DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value) = 0 ,"RN","N/A") )))

mgmar · February 26, 2016, 5:00pm


=IIf(isnothing(Fields!MSS_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) = DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"SAME",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) < DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"RN",
IIf(DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!RN_DATE_TIME.Value) > DateDiff("s",ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!MSS_DATE_TIME.Value),"MSS","N/A") )))

this worked for me sorry for being such a noobie