This content has been marked as final. Show 5 replies
I try the similar:
TIMESTAMPDIFF(SQL_TSI_MINUTE, CAST('14:22' AS TIMESTAMP), CURRENT_TIME(2))
and I got an error:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtExecute: select distinct D1.c1 as c1, ROUND( ( TO_DATE('1900-01-01 12:05:33' , 'YYYY-MM-DD HH24:MI:SS') - CAST( cast('14:23' as DATE) as DATE) ) * 1440 ) as c2 from (select distinct T21473.PROD_CATEGORY as c1 from PRODUCTS T21473 ) D1 order by c1, c2. [nQSError: 17011] SQL statement execution failed. (HY000)
So the syntax is:
TimestampDiff (interval, timestamp-expression1, timestamp-expression2)
If you harcode it:
Timestampdiff(SQL_TSI_MINUTE, TIMESTAMP '2009-04-28 12:00:00', current_timestamp)
The results is difference in minutes.
So you just need to put 'the right expression' in the second place. Try to concatenate current date with your time value and place it insted of TIMESTAMP '2009-04-28 12:00:00' because this inputs only timestamp format.
Hope this helps.
How do i convert my Time column(HH:MM) to a timestamp datatype so as to use it in the function?
This will work:
EVALUATE('TO_TIMESTAMP(TO_CHAR(%1, %2)||%3, %4)' as TIMESTAMP, CURRENT_DATE, 'yyyy-mm-dd', *'12:00:00'*, 'yyyy-mm-dd hh24:mi:ss'),
Basically, you are using Oracle TO_TIMESTAMP and TO_CHAR functions with EVALUATE.
Instead of *'12:00:00'* you put time column that you have defined.
Genius Goran, many many thanks.
I'm glad that this works for you.