DateDiff date,hour and minute Under 24hrs

SSAS and SSRS
1

Hi Everyone,

I have an expression comparing 2 dates with time ex;
11/2/2015 13:30
11/3/2015 11:30

these two dates should be 'WITHIN 24' but its marking them at '24 TO 48'

=IIF(DateDiff("h", Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) <= 24, "WITHIN 24",

IIF(
DateDiff("h", Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) > 24
AND
DateDiff("h", Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) <= 48, "24 TO 48", "OVER 48"))

Thanks in advance!

--

M

2

Is your date format MDY or DMY? If it is DMY, it could be interpreted as Feb 11 to March 11.

If it is MDY, your expression should indeed show "WITHIN 24" for the example you posted.

1 Like
3

I meant for both dates to be in feb sorry typo
but I got the fix

=IIF(DateDiff(DateInterval.Day,Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) <=1, "WITHIN 24",

IIF(
DateDiff(DateInterval.Day, Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) > 1
AND
DateDiff(DateInterval.Day, Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) <= 2, "24 TO 48", "OVER 48"))


just ended up doing day instead of hour

Thanks for the reply James

4

Try the syntax of
DATEDIFF(ss, SELECT MAX(startDate) FROM myTable, SELECT MAX(EndDate) FROM myTable).
It comes from this source:
DateDiff to the second

5

Okay
would this work in SSRS?
My other solution using DateInterval.Day is wrong.. because its counting those with over 24 hours"WITHIN 24"

6

No, it wouldn't work in SSRS. That is T-SQL.

The original expression that you posted should work if your dates are being interpreted as MM/DD/YYYY. I created a test report using your expressions, and it does work. See screenshots below. So if it does not work for you, there is something else that is in play here. One possibility is that the dates are being interpreted as DD/MM/YYYY, in which case 11/2/2015 would be interpreted as February 11th and 11/3/2015 would be March 11.

Capture1.JPG930×189 29.5 KB

The result is the following:

7

The problem I am having with this expression is that this should be counted as 'WITHIN 24'
but its showing 24 TO 48

the format is MM/DD/YYYY
not sure why

8

It is probably something very specific to your data or environment that you would have to debug. Start with the simple example that I posted - i.e., two text boxes with the dates you want and a third one with the expression.

In your expression you have something like this:
DateDiff("h", Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value)

That looks like you are taking the difference between the data from a dataset for the second parameter and the data from a TextBox for the third parameter. That does not sound right based on the screenshot you posted. Look at what TextBox11 is using the properties dropdown menu and selecting TextBox1. I would have thought that your expression should be something like

DateDiff("h", Fields!PROSPECT_ADMIT_DATE.Value,Fields!PROSPECT_DISCHARGED_DATE.Value)

1 Like
9

TextBox11 isn't a dataset field..
That could be the reason its taking the quickest time

=IIf(ISNOTHING(Fields!MSS_DATE.Value),ReportItems!RN_DATE_TIME.Value,
IIf(ReportItems!RN_DATE_TIME.Value= ReportItems!MSS_DATE_TIME.Value,ReportItems!RN_DATE_TIME.Value,
IIf(ReportItems!RN_DATE_TIME.Value < ReportItems!MSS_DATE_TIME.Value, ReportItems!RN_DATE_TIME.Value,ReportItems!MSS_DATE_TIME.Value)))

Maybe I would need to incorporate this somehow into the first expression I posted so that it would take Dataset data...
Ive' tried a switch statement but with no success going to attempt other methods...

-- update I successfully combined both statements with no end results
My belief is that its within the date/time itself on how its formatted...

10

SOLVED

DateInterval.Day,Fields!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value

Changed to

DateInterval.Day,ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value

Thanks for help
I appreciate it!