Time checks with dates

ai_zaviour · #1 (**permalink**) 01-18-12, 04:26

Hello all..

Got a small problem to be solved

I have a Table which have ID and 2 date columns and One time field..

So what i want is when my application insert data with in entered date range the time should not be there..

Plz refer the attachment

For ID 001

FROM_DATE = 1/2/2012
TO_DATE = 1/16/2012
TIME = 15:10:00

So i want to make sure user can't enter same time [15:10:00] for ID 001 for above mention date range..

How can i do this..

this gives me no luck

SELECT ID
FROM HORSE_EVENTS
WHERE FROM_DATE BETWEEN :PARA1 AND :PARA2
AND TO_DATE BETWEEN :PARA1 AND :PARA2
AND TIME = :PARA3

tonkuma · #2 (**permalink**) 01-18-12, 07:04

Please publish sample/test data in one of the format of
(1) DDL and INSERT statement.
or
(2) WITH common-table-expression.

Anyway, try an INSERT statement something like this(not tested)

Code:

INSERT INTO horse_events
SELECT new_id , new_from_date , new_to_date , new_time
 FROM  sysibm.sysdummy1
 WHERE NOT EXISTS(
       SELECT 0
        FROM  horse_events h
        WHERE h.id        = new_id
          AND h.from_date < new_to_date
          AND h.to_date   > new_from_date
          AND h.time      = new_time
       )
;

Internet Services

Web Development

Digital Marketing

Consumer Tech