MQSeries.net :: View topic

andrewl68 · Posted: Thu Oct 16, 2003 4:39 am Post subject: Subtracting Timestamps

I am trying to get the interval in days between two timestamps. I have included my code below. I am getting an error requesting an interval qualifier. I am sure it is my syntax.... any help would be appreciated.

DECLARE SCRATCHDATE TIMESTAMP;
DECLARE SCRATCHDATE2 TIMESTAMP;
DECLARE SCRATCHTIME CHAR;
DECLARE INDATE TIMESTAMP;

DECLARE TIMEDIFF INTERVAL;
DECLARE TIMEDIFF2 INTERVAL;

SET SCRATCHDATE = CURRENT_DATE;

SET T = 1;

WHILE T <= S DO

IF ("InputBody"."TSYS_CRDRIN_LICI"[T]."DateLastCardRequest") <> '0000000' THEN
SET SCRATCHTIME = SUBSTRING("InputBody"."TSYS_CRDRIN_LICI"."TimeLastCardRequest" FROM 1 FOR 2) || ':'
|| SUBSTRING("InputBody"."TSYS_CRDRIN_LICI"."TimeLastCardRequest" FROM 3 FOR 2)|| ':'
|| '01';
SET INDATE = (CONVERTDATE("InputBody"."TSYS_CRDRIN_LICI"[T]."DateLastCardRequest") || ' ' || SCRATCHTIME);
SET TIMEDIFF = CAST((SCRATCHDATE - INDATE) AS INTERVAL DAY);
END IF;
SET T = T + 1;

END WHILE;