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
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?
Quote from: gosoccer on 19 Jun 2017 01:06:28 PM
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.
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.
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.
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. :) :)