SQLTeam.com | Weblogs | Forums

SSRS Datediff Null dates


#1

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


#2

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"))))


#3

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


#4

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


#5

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"))))


#6

you can always use your old method if this fails


#7

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


#8

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


#9

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


#10

=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") )))


#11

yeah I tried that throws errors
ughh :cry:


#12

what is the error?


#13

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'.


#14

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


#15

you need to know what is producing 0 ?


#16

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") )))


#17
=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