Trying to take differences between several date/ti...
# general
e
Trying to take differences between several date/timestamp column in ODBC. Tried a bunch of combinations with TO_NUMBER / TO_CHAR / TO_DATE, but mostly ending up with
Arithmetic operations on data type:12 not
supported
has anybody run into this before?
a
I have not, but I did a quick web search for Oracle data type 12 and found this page: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021 Which makes me think that maybe data type 12 is the Date type. What's your full formula? Maybe the problem is that it's trying to use arithmetic operators or functions on a Date or something that's not numeric at the point where it's being evaluated against the arithmetic operators or functions.
e
I was trying
TO_NUMBER(TO_DATE(TO_CHAR(TRANSACTION.custbodyddate_confirmed, 'mm/dd/yyyy'), 'mm/dd/yyyy') - TO_DATE(TO_CHAR(TRANSACTION.trandate, 'mm/dd/yyyy'), 'mm/dd/yyyy'))
What's even funnier is, even for some reason the number difference is failing
TO_NUMBER(NVL(TO_CHAR(TRANSACTION.custbodyddate_confirmed, 'J'), 1)) - TO_NUMBER(TO_CHAR(TRANSACTION.trandate, 'J'))
Looks like it should work, but failing with same error
a
Hmm I can't figure it out either by looking at it. In this situation I usually try to build the same thing in a char/string/text kind of field, starting with the innermost parenthesis block of one part of the formula, then adding things around it one at a time until I find the part that's causing the error. I also try substituting literals for each operand at first, to check my assumptions and be sure that it's really an issue with my formula and not the database column data type or something else like that.