Subtracting time fields

Database

Subtracting time fields

I have two time fields (starttime and endtime). I’m wanting to subtract the start from the end and I keep receiving the error “Invalid operation on an ANSI datetime or interval value.

Starttime = 17:41:05
endTime = 18:45:14

Help. I’ve tried the extract function, I’ve tried the cast function but I keep getting the same error.
4 REPLIES

Re: Subtracting time fields

Hi,

CAST to TIME(0) will work .
Eg.

select cast(strttime as time(0)) - cast(endtime as time(0)) from timetbl;

****
timetbl
strttime endtime
12:12:30 12:12:31

Is it helpful for you .

Re: Subtracting time fields

Is it helpful for you .

NO
Junior Supporter

Re: Subtracting time fields

Use Interval data types:

CREATE TABLE MY_DB.PRUEBA02
(
ID_N INTEGER NOT NULL,
START_TIME TIME(0) NOT NULL,
END_TIME TIME(0) NOT NULL
)
PRIMARY INDEX (ID_N)
;

*** Table has been created.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 VALUES (1, '12:00:00','12:15:00');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT * FROM MY_DB.PRUEBA02 ;

*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.

ID_N START_TIME END_TIME
----------- ---------- --------
1 12:00:00 12:15:00

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT ID_N,
START_TIME,
END_TIME,
CAST( CAST(END_TIME AS CHAR(8)) AS INTERVAL HOUR TO SECOND(0)) -
CAST( CAST(START_TIME AS CHAR(8)) AS INTERVAL HOUR TO SECOND(0)) DIFF
FROM MY_DB.PRUEBA02;

*** Query completed. One row found. 4 columns returned.
*** Total elapsed time was 1 second.

ID_N START_TIME END_TIME DIFF
----------- ---------- -------- -----------
1 12:00:00 12:15:00 0:15:00

HTH

Cheers.

Carlos.
Enthusiast

Re: Subtracting time fields

Perhaps the following may suit?
SELECT (TIME '18:45:14'- TIME '17:41:05') HOUR TO SECOND(0);