Hi,
I have a custom record that contains a timestamp field called publish_date. I want to find the difference between sysdate and publish_date and I have been unable to perform this operation through the odbc driver.
Option 1) Use systimestamp
select systimestamp - publish_date from myrecordtable
ERROR: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK
SQL Engine]Column:systimestamp not found.[10125]
Option 2) Cast sysdate and publish_date to date
select cast(sysdate as date) - cast(publish_date as date) from myrecordtable
ERROR: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK
SQL Engine]Incompatible value type specified for column:CAST(sysdate())-CAST(publish_date).
Column Type = 9 and Value
Type = 8.[10176]
Option 3) Cast using to_date
select TO_DATE(TO_CHAR(sysdate, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') - TO_DATE(TO_CHAR(publish_date, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') from myrecordtable
ERROR: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK
SQL Engine]Arithmetic operations on data type:12 not supported.[10154]
Any ideas?