Error for calculation in Report Studio/Workspace Advanced (Relational)

[gosoccer]

Hi guys,

I'm getting an error  on the following. Could you please help.
if [Completion Date] - [Creation Date] > 180 then ('not good') else
('good')

UDA-QOS-0006 
UDL- SQL- 0460

gENERAL ERROR OCCURED. LOCAL PROCESS. gREATER is not the right operation
Thanks a lot

[BigChris]

I don't think the calculation will actually bring back 180...it's more likely to be some sort of interval. Is there a function that's something like _days_between?

[Lynn]

Hi guys,

I'm getting an error  on the following. Could you please help.
if [Completion Date] - [Creation Date] > 180 then ('not good') else
('good')

UDA-QOS-0006 
UDL- SQL- 0460

gENERAL ERROR OCCURED. LOCAL PROCESS. gREATER is not the right operation
Thanks a lot

Are those fields date data type or date/time data type? Assuming they are not integer representations of dates then Chris is correct in saying that the result of the subtraction will be an interval data type.

What does 180 represent? Days? Hours? Minutes? Seconds?

Assuming both are date (not date/time) and the 180 represents days, then the result of the subtraction will be a day interval which you can cast to integer and compare to 180.

Code Select Expand


case
  when cast( [Completion Date] - [Creation Date], integer ) > 180
      then 'not good'
  else 'good'
end


If your fields are date/time or if the 180 represents something else then the above is not correct.

[BigChris]

There is a _days_between function...here's the help text for it:

_days_between ( date_expression1 , date_expression2 )
Returns a positive or negative number representing the number of days between "date_expression1" and "date_expression2". If "date_expression1" < "date_expression2", then the result will be a negative number.


Example: _days_between ( 2002-04-30 , 2002-06-21 )
Result: -52

As Lynn says, if you're looking for something other than days you'll need to do something much more complicated.

[gosoccer]

Excellent. Thanks so much. I'm in good shape. Both approaches are working using _days_between and cast( [Completion Date] - [Creation Date], integer ).

I should know the stuff by now.
Thanks for your time Chris and Lynn.  :) :)