Hello all

I have a problem when taking time difference..

The table have a TO_TIME and FROM_TIME time columns..

So when a data enters.. I need to check whether this enter data exists in the above columns..

I have tried something like this...

SELECT * FROM PROGRAMME_SCHEDULE_DTL
WHERE SCHEDULE_ID = '87'
AND '00:00:01','07:00:00' NOT BETWEEN TO_TIME AND FROM_TIME

i know this i wrong...

Any one have ideas..

This is the table structure

CREATE TABLE "DB2ADMIN"."PROGRAMME_SCHEDULE_DTL" (
"SCHEDULE_ID" INTEGER NOT NULL,
"SEQUENCE" INTEGER NOT NULL,
"FROM_TIME" TIME,
"TO_TIME" TIME,
"PROGRAMME_ID" INTEGER NOT NULL,
"DESCRIPTION" VARCHAR(500),
"DEFAULT_URL" VARCHAR(150),
"ACTIVE_STATUS" INTEGER NOT NULL DEFAULT 1
)

you say yet your sql is looking for a
time LT FROM_TIME OR GT TO_TIME
(yes I know, I reversed your BETWEEN)

instead of negative which will return useless rows
if you are indeed looking to see if the TIME already exists,

why don't you

SELECT 1
FROM PROGRAMME_SCHEDULE_DTL
WHERE
:ENTERED-TIME BETWEEN FROM_TIME AND TO_TIME

SQLCODE = 0 means the time exists
SQLCODE = +100 means time does not exist
which is what you said you wanted.

in four years i provide a decent answer to a question
and the topic starter does not return.

It happens too often. Every regular here will have this experience.

Don't take it personally.

Thanks for your contribution and keep coming back.

Hai
Sorry man...

I was too busy with work and could not log in to the forum for a while..

Thanx for the solution you provided..

It worked..

ahhh! the feeling of being appreciated, nothing beats it.
thx ai zaviour.