Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: Select rows created between 2 Pm and 5 PM

    Hello,

    can someone please let me know how can I select all rows inserted in a table created between 2 Pm and 5 Pm since yesterday.

    I have something like this but not sure how to select between 2pm and 5pm.

    select * from table1 where trunc(create_dt) >= trunc(sys-1)

    can someone please help

    Thanks
    Pavan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use TO_CHAR to obtain the HOURS and then use BETWEEN for the desired range
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As anacedent said.

    select * from table1 where TO_CHAR(create_dt,'HH24')) BETWEEN '14' AND '17';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not a native English speaker; I guess you got it right, but I have difficulties in understanding what "since" means here:
    select all rows inserted in a table created between 2 Pm and 5 Pm since yesterday.
    Are these records that were created yesterday between 2 PM and 5 PM?
    Or are these records created any day after yesterday between 2 PM and 5 PM (which would be ... what? Yesterday and today?)

    Anyway: rather than TO_CHARing, I'd opt for another approach as TO_CHAR tends to invalidate positive "date_column" index affects:
    Code:
    SQL> select sysdate - 1 yesterday,
      2    trunc(sysdate - 1) + 14/24 yesterday_2_pm,
      3    trunc(sysdate - 1) + 17/24 yesterday_5_pm
      4  from dual;
    Therefore, assuming that the OP wants records created yesterday between 2 PM and 5 PM, that would make
    Code:
    select *
    from your_table
    where date_column between trunc(sysdate - 1) + 14/24
                          and trunc(sysdate - 1) + 17/24;

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Littlefoot is right, I missed the "since" nice catch!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Oct 2005
    Posts
    92

    Select rows created between 2 Pm and 5 PM

    Thank you all.

    I was looking for records created yesterday between 2 PM and 5 PM. your suggestions helped me.

Posting Permissions

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