I ran the sql like this.

select, TIME(FieldA), TIME(FieldB)
(Time(Field A) - Time(FieldB)) as NET

The results were

18:35:47, 18:32:45, 302

I want to show the NET as 3:02 not 302.

Please help...

You probable should create a UDF to do what you want:

CREATE FUNCTION PRODUCTION.TIME_DIFF(T1 TIME,T2 TIME)
RETURNS VARCHAR(8) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
DECLARE tDIFF DECIMAL(6,0);
DECLARE T_STRING VARCHAR(8);
DECLARE HH char(2);
DECLARE MM char(2);
DECLARE SS char(2);

SET tDIFF = T1 - T2;

SET HH = SUBSTR(DIGITS(HOUR(tDIFF)),9);
SET MM = SUBSTR(DIGITS(MINUTE(tDIFF)),9);
SET SS = SUBSTR(DIGITS(SECOND(tDIFF)),9);

SET t_STRING = HH || ':' || MM || ':' || SS;

RETURN T_STRING;

END@


HTH

Andy

The result of subtracting one time value from another is timestamp duration, which is a DECIMAL(20,6) in the format:

yyyymmddhhmmss.zzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and microseconds.

This basically means that if you want any fancy formatting of this number you'll have to do it yourself. For example, you could convert it to a string and then parse the string to extract minutes and seconds.

Actually subtracting times (not timestamps) is a time duration which is DECIMAL (6,0). Subtracting timestamps is a timestamp duration as you describe.

Andy

will output from TIMESTAMPDIFF function make the customization any easier ?

Cheers
Sathyaram

thanks for the responses.
I tried using timestampdiff. It works but the arguments options a re limited.
ie it will either give in min or seconds etc. No HH:MMS format..


anyway thanks a ton to all for the great responses.