ssrs date expression

aneilg
aneilg used Ask the Experts™
on
i have the following expression.

=IIf(DateDiff("d", Fields!Max_Date.Value, Now()) = 0, "Green",
Iif(DateDiff("d", Fields!Max_Date.Value, Now()) > 7,  "Orange",
Iif(DateDiff("d", Fields!Max_Date.Value, Now()) > 30, "Red", "Black") ) )

but its only evaluating the day and not the month.
my date is 22-05-2012.

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SThayaTechnical MAnager

Commented:
your expression evaluate only the no of days difference between now() and Fields!Max_Date.Value . because you have mentioined the interval as "d"



Tell me what is your exact requirement?

Author

Commented:
its the same requirement, but evaluate the day and month.
as an example.
22-05-2012 wiil be the same as 22-04-2012, which is incorrect.
BI Consultant
Most Valuable Expert 2011
Commented:
"but its only evaluating the day and not the month."

Are you sure about that?  Because that's not how DateDiff works.  When run today, you should be getting 0 for 22-05-2012 and 30 for 22-04-2012.  In other words, months (and years for that matter) are automatically taken into account because you're getting the number of days between the two days specified. Which is what you need, right?
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Now I noticed, the error is in your expression: >30 is also >7 and you've put the >7 condition first.

Try this instead:

=Switch(
  DateDiff("d", Fields!Max_Date.Value, Now()) > 30, "Red",
  DateDiff("d", Fields!Max_Date.Value, Now()) > 7,  "Orange",
  DateDiff("d", Fields!Max_Date.Value, Now()) = 0, "Green",
  true, "Black")

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial