Date comparison 1

[roberthagan]

I'm in the process of converting some Crystal reports to RS
I need to do things like count for each person how many decisions are due in the current month.
I have in my query (in Oracle 8i) a calculation for DecisionDue_Date, and FirstDayofMonth and LastDayofMonth. I thought I could just find due_dates between the First and Last days:

IIF(DecisionDue_Date >= FirstDayofMonth AND DecisionDue_Date <= LastDayofMonth, 1,0)
But, that always = 0.

To my surprise, even IIF(DecisionDue_Date >= FirstDayofMonth, True, False) is always False.

I found thread1462-1252630 which says "...faff around with VB expressions to convert a date to an integer..." Do I have to do this in my situation? There is no standard function to handle this? (I couldn't find one.)

Thanks
Bob Hagan

 

[AlexCuse]

What are FirstDayOfMonth and LastDayOfMonth?

Perhaps DatePart would be useful here?

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[roberthagan]

First and LastDayOfMonth come out of my query.

TRUNC(SYSDATE,'MONTH') AS FirstDayofMonth,
Last_Day(SYSDATE) AS LastDayofMonth

The other thread suggests doing date stuff in the query if possible. Oracle 8 has a "Decode" function as the equivalent of if/then/else, but my data can have multiple referrals etc., so I have to use
MAX(DECODE(referralcode,referral_dt,null) inside of the decode to do the date comparision. Picky. I was trying to do something quick, and Crystal lets you do date comparisions, so I was surprisd when that didn't work.

So back to my original question: is there something I'm missing in Reporting Services that would make this easy, at least possible, or is this a limitation that has to be worked around? Should I write a function and add it to a custom assembly?

 

[AlexCuse]

Hm. I am not familiar with Oracle, but a case statement within your query seems like it would be a reasonable solution.

One question is, can you even reference fields like that in your report? Perhaps it is causing some odd implicit conversion? I've always used Fields!ColumnName.Value. So you might try changing that first.

If that doesn't work, try something like this (OTTOMH, not tested)

=IIF(DatePart(DateInterval.Month, Fields!DecisionDue_Date.Value) = DatePart(DateInterval.Month, Fields!FirstDayofMonth.Value) AND DatePart(DateInterval.Year, Fields!DecisionDue_Date.Value) = DatePart(DateInterval.Year, Fields!FirstDayOfMonth.Value), 1,0)

Hope this helps,

Alex


[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[roberthagan]

Case was introduced into Oracle 9, so I don't have it available for this.

I didn't qualify the fields here but they are correct in the function.

Your approach with datepart works. I'm not sure I would have ever thought of that.

Thanks much.