We've a scenario of subtracting two timestamp fields in a procedure. The scenario is like below.
We've set a variable value as follows.
SET v_diff_tm= current_timestamp(3);
SET v_debug_message = sp_debug( CAST( (CURRENT_TIMESTAMP(1) (FORMAT 'HH:MI:SS') ) AS CHAR(21)) || ',' || CAST(((CURRENT_TIMESTAMP(0) - v_diff_tm) SECOND(3))AS CHAR(21))
Our procedure is executing successfully from the past one year in production but suddenly it has stared failing with 'Interval Field Overflow' issue at above statement.
1.Can you please let us know what could be the reason for this sudden failure.
2.We've identified it has failed due to the above casting operation to SECOND(3), the returned seconds value is more than the given range. What can be the fix to overcome this issue.As the max value for INTERVAL SECOND is 4 ,can we change the above INTERVAL from SECOND(3) to SECOND(4).
Will it solve the problem permenantly? We need your help at the earliest. Thanks in advance !!