PnP PnP - 11 months ago 131
SQL Question

SSRS comparing dates in an expression

I have some basic expressions to highlight certain fields:

=IIF(Fields!Failure.Value > 0, "Red", "Transparent")

However, I have another field that contains dates in the following format:

22/08/2016 22/08/2016 - each field can contain multiple dates.

This is stored in SQL as a VARCHAR.

I need to run a similar expression in my report to achieve the following:

If the date is 1 day older than the date the report is run, highlight the field. If the date is greater than 1 day older, highlight the field a different colour.

I#m familiar with basic expressions, but I can't think of an easy way to obtain the current date, and then compare between the two.

Answer Source

As others have said, you really shouldn't be using hacks like this...

But, this should work for you:

=iif(Len(Replace(Replace(Fields!DateField.Value," ",""), Format(Today, "dd/MM/yyyy"),"")) = 0, "Transparent" ,iif(Len(Replace(Replace(Replace(Fields!DateField.Value," ",""), Format(Today, "dd/MM/yyyy"),""), Format(Today().AddDays(-1), "dd/MM/yyyy"),"")) = 0, "Green", "Red"))

Essentially, remove the joining character (in this case, space) and then replace all instances of the current date in the given format. If there are any characters left, you have a date that doesn't match today. Then take that value and repeat for any instances of yesterday.

Obviously this will fall down if your date formatting changes.
But then you already knew that comparing dates as strings was a bad idea, right...