select based on time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: select based on time

  1. #1
    Join Date
    Mar 2004
    Posts
    52

    select based on time

    table: orders
    columns: orderid,order,order_date

    how do i select all the orders between 12pm and 2pm

    Thanks
    R

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Code:
    select * from xyz where dt >=sysdate+12/24 and dt
    
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    thanks

  4. #4
    Join Date
    Mar 2004
    Posts
    52
    hi ,

    Is there any other way to do this?

  5. #5
    Join Date
    Jul 2005
    Posts
    4
    Quote Originally Posted by RamanaSQL
    table: orders
    columns: orderid,order,order_date

    how do i select all the orders between 12pm and 2pm

    Thanks
    R
    Assuming you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type, one way is:

    Code:
    select 
        orderid,
        order,
        order_date
    from 
        orders
    where  
        to_number(to_char(order_date, 'HH24MI')) 
            between 1200 and 1400
    Eddie Awad
    awads.net

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    Let me try this ..


    Thanks

    R
    Last edited by RamanaSQL; 08-01-2005 at 05:31 PM.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Quote Originally Posted by eddieawad
    Assuming you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type, one way is:

    Code:
    select 
        orderid,
        order,
        order_date
    from 
        orders
    where  
        to_number(to_char(order_date, 'HH24MI')) 
            between 1200 and 1400

    which is worse

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Jeff's solution only works for the current day.

    Pando, I can't see any way NOT to use a function on the date column if we are talking about any date. So chose between:

    a) order_date - trunc(order_date) between 12/24 and 14/24

    b) to_char(order_date, 'HH24MI') between '1200' and '1400'

    So in the general case, if you need high performance, I'd go for (b) and create a FBI (assuming these are exceptions - i.e. only a small fraction of the table is selected.)

    **** OOOPS
    Yes, (a) and (b) are both candidates for an FBI. So ignore previous paragraph.
    Last edited by DaPi; 08-02-2005 at 03:05 PM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Jul 2005
    Posts
    4
    Quote Originally Posted by pando
    which is worse
    Pando, "worse" is a very vague word. My assumptions were:
    Quote Originally Posted by eddieawad
    you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type
    Based on the assumptions above, how would you make the query "better"? other than what DaPi suggested: removing the to_number, and/or creating a function based index.
    Eddie Awad
    awads.net

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I didnt see the ANY date so I make a pardon

Posting Permissions

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



Click Here to Expand Forum to Full Width