Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Time checks with dates

    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
    Attached Thumbnails Attached Thumbnails 1.JPG  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
           )
    ;
    Last edited by tonkuma; 01-18-12 at 08:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •