display image on a report based on two date

I am using sql server 2005 reporting service to generate report base on a database. There are two columns which are datetime type ColumnA and ColumnB. The report would display a KPI image on this report by comparing these two columns.Below is the expression for selecting image

SWITCH(DateDiff("d",Fields!ColumnA.Value,Fields!ColumnB.Value)<0,"kpi_r",
       DateDiff("d",Fields!ColumnA.Value,Fields!ColumnB.Value)>0,"kpi_g",
       DateDiff("d",Fields!ColumnA.Value,Fields!ColumnB.Value)=0,"kpi_y")

For most of the records, the image is correct. Only for one record, the result is very strange. For this record

ColumnA=2010-04-23 08:00:00 ColumnB=2010-04-22 17:00:00 It would display kpi_r, it displayed kpi_y. I have checked the value of DateD开发者_如何转开发iff(d,Fields!ColumnA.Value,Fields!ColumnB.Value) in the SSMS, the value is -1. Why does it display kpi_y? Does anyone meet this problem before?

Best Regards,

The difference is that the SSMS DATEDIFF function counts the interval boundaries between the two dates whereas ReportBuilder counts the actual intervals. Within SSMS if you cross midnight you have triggered a day boundary so in your example you get -1. In ReportBuilder it is looking for 24 hours to be between the two values so you get 0. If you change the time on ColumnA to be '2010-04-23 17:00:00' you will see the value changes to -1 as you expected. For your comparison it would probably make sense to strip the time component from ColumnA and ColumnB when you do this SWITCH statement.

the above answer is spot on.

here's a few ways to strip time off a date depending on your preferences:

1. do it in RS: use and expression like dateserial(year(Fields!ColumnA.Value),month(Fields!ColumnA.Value), day(Fields!ColumnA.Value)) in your switch expression

2. do it in SQL: use an expression like cast(round(cast(ColumnA as float),0,1) as datetime) in your query