Analytics Software

Hello experts,

I want to know How to use TimeStampDiff function appropriately.

My requirement is I have a column in the physical layer say time. It is of datatype varchar(5). It stores the values in HH:MM format.

I want to have the difference between the current time and this column.

For current time, Im using function in OBI Answers: CURRENT_TIME(2) where 2 denotes the precision of seconds.

The formula Im using is TIMESTAMPDIFF(SQL_TSI_MINUTE, CAST("Query Time".Time AS TIMESTAMP), CURRENT_TIME(2)).

This is throwing the following error:

ORA-01843: not a valid month at OCI call OCIStmtExecute: select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, max(D1.c6) over () as c6 from (select distinct D1.c2 as c1, D1.c3 as c2, D1.c4 as c3, TO_DATE('1900-01-01 03:54:07' , 'YYYY-MM-DD HH24:MI:SS') as c4, D1.c1 as c5, D1.c1 as c6 from (select max(T45654.TOTAL_TIME_SEC) as c1, TRUNC(T45677.DAY_DT) as c2, ROUND( ( TO_DATE('1900-01-01 03:54:07' , 'YYYY-MM-DD HH24:MI:SS') - CAST( cast(T45654.START_HOUR_MIN as DATE) as DATE) ) * 1440 ) as c3, T45654.START_HOUR_MIN as c4 from S_ETL_DAY T45677, S_NQ_ACCT T45654 where ( T45654.START_DT = T45677.DAY_DT and T45654.SAW_SRC_PATH = '/shared/PI BOOK of Business - P2/AE Home Page/Search/P2 - Trust Request' and TRUNC(T45654.START_DT) = TO_DATE('2009-03-09' , 'YYYY-MM-DD') and TRUNC(T45677.DAY_DT) = TO_DATE('2009-03-09' , 'YYYY-MM-DD') ) group by T45654.START_HOUR_MIN, TRUNC(T45677.DAY_DT) ) D1 ) D1 order by c1 desc, c2, c3, c4. [nQSError: 17011] SQL statement execution failed. (HY000).

Can anybody help me get the right formula?

thx,
parag