1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problems using Timestamps for a start and end date

Discussion in 'SQL PL/SQL' started by craig21, Apr 12, 2012.

  1. craig21

    craig21 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi all, if anyone has any advice it will be appreciated :)!

    I have a table which stores bookings using timestamp data type, the table consists of a booking id, start date and end date (dates booked table) and the booking table consisits of the cars reg_no and customer_no etc.

    If I needed to find out which cars were booked between the 6th-20th April (just as an example), how would I go about it?
    I tried this but it returned nothing, but I know there was a car booked between these dates, I tried this way:

    select b.registration_no, b.make, b.model_no
    From car b, booking bb, dates_booked bbb
    Where b.registration_no = bb.registration_no
    And bb.booking_id = bbb.booking_id
    And start >='06-APR-12'
    And end <= '20-APR-12';

    Thanks.
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi dude

    Use trunc() and to_date() functions like below...


    select b.registration_no, b.make, b.model_no
    From car b, booking bb, dates_booked bbb
    Where b.registration_no = bb.registration_no
    And bb.booking_id = bbb.booking_id
    AND trunc(start) >= to_date('06-APR-12','dd-MON-yy')
    AND trunc(end) >= to_date('20-APR-12','dd-MON-yy')


    Regards
    Samba
     
    craig21 likes this.
  3. craig21

    craig21 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    That worked, thanks alot!
     
  4. craig21

    craig21 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    That returned cars that were booked between those dates, which is great. But I also have a booking that started on the 04-APR-12 and ended on the 15-APR-12, which wasn't returned when running this query but this record is effectively booked for part of that dates in the query.

    Regards Craig
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It won't be returned as it isn't scheduled to end on April 12.
     
    craig21 likes this.