converting vargraphic to time

[lexlukkia]

Good day masters.

i have this problem in cognos report studio converting string to time.

01:20:00 (hh:mm:ss). i check the datatype it is vargraphic.
im trying to convert it to time because im going to deduct of that to another time. unfortunately... i am unable to do that task.
i've tried.
cast([dataitem] as time) = error (datetime value is incorrect)
cast([dataitem], time) = error (datetime value is incorrect)
cast([dataitem] as timestamp) = error (datetime value is incorrect)
cast([dataitem], timestamp) = error  (datetime value is incorrect)
string2time([dataitem]) = error (datetime value is incorrect)
strtotime([dataitem]) = error (no such function found)
time([dataitem]) = error (datetime value is incorrect)
to_date([dataitem]) = error (datetime value is incorrect)
format([dataitem],'hh:mm:ss') = error

what im about to do is... after i convert it to time... i going to convert it to seconds. then also convert the dataitem2 to seconds and deduct dataitem1 to dataitem2.

but the thing is, i'm having a hard time converting the dataitem1. T_T

what seems to be i'm doing wrong.

[lexlukkia]

UPDATE:

I know now why i'm having an error converting my time. it is due to negative value.
i as scroll in time column there are values like (-01:-05:-20).

how do i convert this negative time value to positive value?

[lexlukkia]

nevermind.

what i just do now is split all the value. then convert them to integer.

select statustracking,
cast(substring(statustracking, 1, locate(':', statustracking)-1) as integer) as hourOfTracking,
cast(substring(
   statustracking,
   locate(':', statustracking)+1,
   (locate(':', statustracking, locate(':', statustracking)+1)-locate(':', statustracking))-1) as integer) as minutesOfTracking,
cast(substring(
   statustracking,
   locate(':', statustracking, locate(':', statustracking)+1)+1,
   length(statustracking)) as integer) as secondsOfTracking
from maximo.tkstatus